I don't get what you are trying to show - can you attach a tbwx and mock up what you want to be on the sheet?
Attached. Thank you!
sheet 1 shows expected result - value for last day of the month
sheet 2 shows that tableau aggreagates all days within month and shows the sum, which is not right as the measure shows number of users at the end of period (EoP).
Book2.twbx.zip 22.5 KB
Two new calcs:
- Find out the last day of the month - we lookup the last day, lookup(attr([Date]), last()) and set the window calculations to separate by months (you also have to set the order the rows are listed) - see pic
- A calc to only return a EoP if the date on the row matches the last day of the month
Caveats: May not work if some data is missing - you would need to change the partitioning.
last day of month.twbx.zip 22.9 KB
Works almost smoothly, even on a large data set. Cool. The only unpleasant behavior is "overlapping text" Warning
I was also receiving error (Invaling datetime value) when I tried to use Date dim in the Columns shelf and then view it as a Week number. Then I duplicated Date dimension and it works!
One more observation: if I remove from 'Compute using' the field _UCAT (Date stays), it shows the last non empty value in the _UCAT.
And one more question: would you recommend using Custom SQL or normal blending/joining in this case?
Does it actually overlap text? That's what the if first()=0 in the calc is for. If not, you can just check the do not show again warning.
Unsure about the Invalid datetime warning
I like joining over blending certainly (where it's possible) - custom SQL is great as well if that's your style.
No, it doesn't, just a window pops up.
How would you join those tables I am unioning via SQL. Inner, Left, Right - all of them create multiple measures (one for each table). I'm more familiar with SQL which on the other hand creates some limitations on Tableau side.
Left join within Tableau by the looks of it, though that would mess up your union all - maybe better just with a custom SQL and left join