I have to calculate the total amount of clients that made a purchase by day and month.
My dataset contains all purchases, with an identifier for clients, a date field and a purchase field which indicates whether it was a sale or a return.
So, my intuitive guess was to create a calculated field like this:
The thing is, when I do this and put it in a visual, with the date field as month in the columns, it does not show the same result as using this calculation:
Hi @Zeke - I think even in your first one, it is going to group the sum of purchase by the actual unique date values - it is not going to dynamically change that group by depending on the aggregation you are using in your visual (the only part that will be affected by the fields in the visual is the grouping of distinct_count).
Is your end visual just a KPI with the count, or are you actually plotting this in a pivot table or line chart where you are showing the days/months broken out?
Going under the assumption that if there are no rows of data if the client didnt make a purchase that day/month (as opposed to a row but purchase amount is 0), then I dont think you need all the fancy LACs (though great job figuring these out ).
Couldn’t you just put distinct count of client as your Value and then you can drill up/down from month to day and it will group that aggregation appropriately?
Yes, that’s right. However, even though I am working on a dataset as such, I also have to show the same calculation in a different dataset which has one row per client and day (which means I have rows in which the clients might not make a purchase), therefore the above calculation is needed.
However, In both cases I run into the same inconsistency on the data. Regardless the fact that I do not need the calculation in the purchases dataset, I should not be facing those differences in results, should I?
Hey @Zeke - The differences in the two calcs are expected since these calcs (and specifically the group by/partition parts of them) are computed irrespective of what fields are in your visual. So for instance if you have Date in the partition, it is going to group Purchase by actual date values - it does not dynamically change the date aggregation to Month if you are using Months in your visual instead of Days.
I dont think there is going to be a way to group this calc differently (by day or month) depending on the drill down level (day or month) shown in the visual. You are probably going to need two different visuals and two different calcs. You could implement a parameter to select Day or Month and then using Free Form Layout, overlay these two visuals on top of one another and use Conditional Rendering Rules to show/hide the appropriate visual based on the parameter selection. User doesnt know it’s two different visuals - looks like one to them.
).