Comparing dates in alternate states with a distinct calendar

[Anonymous]

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! 

[Anonymous]

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

[Anonymous]

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.