Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a set of data that has multiple date fields in it. I have therefore opted to use a separate calendar as a date selection may need to apply to multiple date fields on one screen (e.g. selecting January as a date range may be required to show everything that was sold in January as well as everything that was bought in January).
This is fine... I run into issues when I try to compare data from two time periods.
I make two date selections; one in the main state, and one in an alternate state. I would like to show a comparison of the data across the time period selected. e.g. see a selection for January mapped against a period in February.
How do I make the date dimension so that the two lines overlay each other?
I have tried using the following:
IF(
ISNULL(
Aggr(Rank(-Sales_Dt),Sales_Dt)),
Aggr(Rank(-Concat({Benchmark}Sales_Dt)),Sales_Dt),
Aggr(Rank(-Sales_Dt),Sales_Dt))
Attached is a simplified file of the issue: I run into problems because no selection is made in the "sales data" calendar (dates are selected from a separate calendar and reference with a Sales_Dt=P(Calendar_Dt) in the metric expression), so the rank function ranks all values of Sales_Dt, not just those i'm interested in.
There is also not necessarily data for every calendar day so ranking the Sales_Dt may miss out some days in the calendar where there is no data.
I have tried ranking the calendar_dt but can't link Sales_Dt back to it in the dimension.
Hopefully some of that makes sense!
Any help would be much appreciated!
If I'm understanding your problem correctly, you'll have to use set analysis to compare the dates
for example:
sum( {<Calendar_dt = [State 1]::Sales_Dt>} Balance)
Then in another expression:
sum( {<Calendar_dt = [State 2]::Sales_Dt>} Balance)
This will create 2 distinct bars/lines/plots/whatever you're using for each dimension in the chart
Thanks for your response. It is not the expressions I am having trouble with (I am using:
sum({<Sales_Date=P(Calendar_Dt)>}Sales_Volume) and sum({<Sales_Date=P(Comparison::Calendar_Dt)>}Sales_Volume)
But how to calculate the dimension value. I want to compare day 1 of the first selection (e.g. 1st January) to day 1 of the second selection (e.g. 1st February) and map it over a time based dimension.