Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a scenario where I want to create a bar chart where expression requires time duration in set analysis. But Dimension is also a time duration. How to solve this?
Here is Complete Scenario:
I have a Date field. Max Date is Maximum of that date. I want a dimension which always have has 3 values -
1.Maximum date available(Month end of last month in Quarter) in last quarter of Max date.
2.Maximum date available(Month end of last month in Quarter) in last to last quarter of Max date.
3.Max Date.
Calculating these is not a problem.
Now I want Sum of Sales corresponding to three dates in Dimension.Sum of sales corresponding to less that the available dates in dimension.
expression to understand can be sum( {<Date={'<=$(=Date)'}>} Sales) .
How to make this possible.?
I'd create three variables with the expressions to calculate the three dates. Then use these three variables in a set analysis expression:
sum({<Date={ $(vMaxDate), $(vMaxDateOfQuarter), $(vMaxDateOfLastQuarter) }>}Sales)
Hi Gysbert ,
I didn't get you. What will be the dimension then? to show 3 dates. And I want sales of all dates previous to dates in dimension.
I'm probably misunderstanding what you're trying to achieve. Can you post an example with source data and the result table or graphic that should be shown?
Hi Gysbert,
I cant post Data. But let provide u a clearer picture.
I want something like attached screenshot. Expression should solve the purpose as shown in screenshot.
I am creating dimension by individually calculating 3 Dates and concatenating them.
Like :-
If(Date = $(vLastQuarDate),Date) & If(Date = $(vLastToLastQuarDate),Date) & If(Date = $(vMaxDate),Date).
If you can give another way of creating required dimension I don't mind. But it should Solve the purpose.Let me know if you require anything.
Thanks.