I was trying to use a parameter action. Unfortentelly, with parameter action I do not have many option to set up what happened when I deselect one mark. Set action did it properly Thanks, Andy!
Hi Andy, just a quick question. I’m trying to use the set action on a dashboard and want it to filter all the other sheets on the dashboard but I get the error message that the Period field is missing on multiple worksheets. (My period field is the equivalent of your month field, I created a parameter where I can choose if I want to see the day or week). Would you know how to fix this?
The field that you target with the set action must be in each worksheet you are running the action on. Tableau needs this so that it can perform the action. If you put the field on the Detail shelf, then that error message will go away
I practiced this today on my laptop. Thanks again ! I have one more doubt- what does '{ }' stand for and why is it used ? I have never used this but it seems really imp because in 'Sales in Range' calc field I deliberately avoided it and got error !
The {} represent a level of detail expression, which does exactly what its name says, it changed the level of detail. In this case, I do not specify a dimension inside the LOD, therefore the level of detail doesn’t exist, thus giving me the total. This blog post will be super helpful. www.vizwiz.com/2017/04/fixed-lod-pt1.html
Here are a couple videos for you... ua-cam.com/video/ViD3vclgNv8/v-deo.html ua-cam.com/video/ZQi1Uh8bNVQ/v-deo.html (use month instead of year in the calcs) ua-cam.com/video/YuxYr4_l2ww/v-deo.html (use month instead of year in the calcs) If you still have questions, let me know.
Hi Andy, thanks for the video. May I ask whether this approach can work on filter instead of select? I would like to get the min and max month of the filtered viz, but I can’t find the workaround using action to update the Month Set when a new date range is filtered. Appreciate your help pls. Tks!
Hi Andy. Thanks so much for this video - I found it really helpful. I'm trying to adapt it for a value of number of hours (INT value) instead of $$. The issue I'm having is that the field I'm swapping out [Sales] with is a calculated field (called Case Hours) and when I try to create the calculated field "Sales in Range" (I'm named it Hours in Range), I get the "Cannot mix aggregate and non-aggregate arguments with this function" error. Can you suggest a modification to this calculation to get the desire result? BTW I've liked this video and am following your channel - Rene :)
Hi@@vizwiz - thanks for responding. Here's my calculation: {SUM(INT([InRange])*[Hours Case])} ... and here is the Hours Case calculation: SUM( ZN(INT([Time Taken (Hours)])) + ZN(CASE [Time Taken (Minutes)] WHEN "0" THEN 0 WHEN "5" THEN 0.083 WHEN "10" THEN 0.167 WHEN "15" THEN 0.25 WHEN "20" THEN 0.333 WHEN "25" THEN 0.417 WHEN "30" THEN 0.50 WHEN "35" THEN 0.583 WHEN "40" THEN 0.667 WHEN "45" THEN 0.75 WHEN "50" THEN 0.833 WHEN "55" THEN 0.917 ELSE 0 END ))
can you please explain the function of {}. I know it used for LOD but without LOD function you also use it. can you explain it please? not finding clear understanding in google though
Omark, a Boolean returns a 1 or 0. So if it’s in the range, it’s 1, multiplying that by sales to get sales. If it’s not in the range, then you’re multiplying sales * 0, which will always be zero.
Tableau is easy with Andy!! Thank you :)
That's why people call you wizard. Kudos Andy!
I was trying to use a parameter action. Unfortentelly, with parameter action I do not have many option to set up what happened when I deselect one mark. Set action did it properly Thanks, Andy!
Thanks for sharing!
You beauty... Great and simple implementation of such a complex problem.
I really like this quick and compact format. Very efficient !
Your videos have helped me become more comfortable using calculated fields. Thank you!
Great to hear!
Thank you Andy, this is a great guide, love your UA-cam shorts so far
Glad you like them!
Andy is really great!
thank you! your video helps me a lot!
Very clever. Thanks for sharing.
Glad it was helpful!
Thanks Andy
You bet
Outstanding!
Thank you! Cheers!
thx all your content is very useful 🙏🙏
Thank you. Very helpful!
You're welcome!
Amazing video!
Hi Andy, just a quick question. I’m trying to use the set action on a dashboard and want it to filter all the other sheets on the dashboard but I get the error message that the Period field is missing on multiple worksheets. (My period field is the equivalent of your month field, I created a parameter where I can choose if I want to see the day or week). Would you know how to fix this?
The field that you target with the set action must be in each worksheet you are running the action on. Tableau needs this so that it can perform the action. If you put the field on the Detail shelf, then that error message will go away
@@vizwiz Thanks for replying Andy. It was actually a simple fix, I put the in range field in the filter and everything worked fine.
Another great one! Thank you!
...Having trouble calculating the average over the selected range without it producing an error. Any ideas?
It’s probably an aggregation error. Make sure they are all aggregates.
Brilliant as always. Thank you for this
Glad you enjoyed it!
I practiced this today on my laptop. Thanks again ! I have one more doubt- what does '{ }' stand for and why is it used ? I have never used this but it seems really imp because in 'Sales in Range' calc field I deliberately avoided it and got error !
The {} represent a level of detail expression, which does exactly what its name says, it changed the level of detail.
In this case, I do not specify a dimension inside the LOD, therefore the level of detail doesn’t exist, thus giving me the total.
This blog post will be super helpful.
www.vizwiz.com/2017/04/fixed-lod-pt1.html
@@vizwiz Thanks a lot again !
Hi Andy, can we get the sales and the month-on-month increase in the same worksheet? is it possible at all?
Here are a couple videos for you...
ua-cam.com/video/ViD3vclgNv8/v-deo.html
ua-cam.com/video/ZQi1Uh8bNVQ/v-deo.html (use month instead of year in the calcs)
ua-cam.com/video/YuxYr4_l2ww/v-deo.html (use month instead of year in the calcs)
If you still have questions, let me know.
Hi, Andy, I have a question. when creating 'InRange', why is it necessary to wrap the sum expression with the curly brackets?
That makes it a level of detail expression, which results in only a single number, irrespective of the marks in the view.
Hi Andy, thanks for the video. May I ask whether this approach can work on filter instead of select?
I would like to get the min and max month of the filtered viz, but I can’t find the workaround using action to update the Month Set when a new date range is filtered.
Appreciate your help pls. Tks!
A filter won’t work because it limits the data in the view.
@@vizwiz ok thanks!
Hi Andy. Thanks so much for this video - I found it really helpful. I'm trying to adapt it for a value of number of hours (INT value) instead of $$. The issue I'm having is that the field I'm swapping out [Sales] with is a calculated field (called Case Hours) and when I try to create the calculated field "Sales in Range" (I'm named it Hours in Range), I get the "Cannot mix aggregate and non-aggregate arguments with this function" error. Can you suggest a modification to this calculation to get the desire result? BTW I've liked this video and am following your channel - Rene :)
One of the parts of your calculation is not an aggregate. Check that. What’s your calculation?
Hi@@vizwiz - thanks for responding. Here's my calculation: {SUM(INT([InRange])*[Hours Case])} ... and here is the Hours Case calculation: SUM(
ZN(INT([Time Taken (Hours)]))
+
ZN(CASE [Time Taken (Minutes)]
WHEN "0" THEN 0
WHEN "5" THEN 0.083
WHEN "10" THEN 0.167
WHEN "15" THEN 0.25
WHEN "20" THEN 0.333
WHEN "25" THEN 0.417
WHEN "30" THEN 0.50
WHEN "35" THEN 0.583
WHEN "40" THEN 0.667
WHEN "45" THEN 0.75
WHEN "50" THEN 0.833
WHEN "55" THEN 0.917
ELSE 0
END
))
can you please explain the function of {}. I know it used for LOD but without LOD function you also use it. can you explain it please? not finding clear understanding in google though
{} is always an LOD.
❤
❤️
'InRange' is a boolean field, so how does (InRange * sales) work ?
Thanks Andy, I really appreciate that you are taking the time to do this for us! You're the best!
My pleasure Jan!
Omark, a Boolean returns a 1 or 0. So if it’s in the range, it’s 1, multiplying that by sales to get sales. If it’s not in the range, then you’re multiplying sales * 0, which will always be zero.
@@vizwiz Got it. Thanks a lot !!
([Months])>= ([Min Month])
AND
([Months])
You need to make sure everything is aggregated. One of your fields is an aggregate.