Two date selector for the fact table

[adecora]

Hello, I need to create two different date selectors for the same fact table, one in charge os actual sales and the other one for select comparable sales. I was thinking on the following approach where I get three island tables and I manage the different date selectors through set analisys.

Let maxDate = Num(Today());
Let minDate = maxDate - 7;

actual_calendar:
Load
    Date($(minDate) + IterNo() - 1) as actual_date
AutoGenerate
    1    
While
    ($(minDate) + IterNo() - 1) <= $(maxDate);

comparable_calendar:
Load
    Date($(minDate) + IterNo() - 1) as comp_date
AutoGenerate
    1    
While
    ($(minDate) + IterNo() - 1) <= $(maxDate);

fact:
Load
    Peek('actual_date', Mod(RowNo(), 8), 'actual_calendar') as sales_date,
    Pick(Mod(RowNo(), 3) + 1, 'A', 'B', 'C')                as company,
    Round(Rand() * 100 + 100, 0.001)                        as sales
AutoGenerate
    100;

maxDate = ;
minDate = ;

Is possible to get another approach without the use of island tables? In that case what would be the differences in performance???

Thanks

[marksouzacosta]

Hi @adecora,

This is a classic question. You can dominate the possible solutions reading those articles:

Canonical Date:
Canonical Date - Qlik Community - 1463578

Why You sometimes should Load a Master Table several times:
Why You sometimes should Load a Master Table sever... - Qlik Community - 1472008

Linking to two or more dates:
Linking to two or more dates - Qlik Community - 1495322

Regards,

Mark Costa

[Kushal_Chawda]

@adecora  I would not suggest to use Island table as they are not good for performance point of view. You may need a common date calendar which can be linked to both the Dates using flag. You can then utilise that flag in measure to have flavour of both the dates. Checkout below link as an example

https://community.qlik.com/t5/App-Development/Create-a-common-Calendar/m-p/2064060#M700

[Vegar · Accepted solution]

Another approach to solve this (if I understand your scenario correctly) is to use alternate states. With alternate states you can compare two sets of selections in the application with each other. 

You could start exploring this concept on this page: Using alternate states for comparative analysis 

[adecora]

Thanks, actually this approach works good, although it can be not really meaninfull to the end user (on checking selection state)

[adecora]

@marksouzacosta, @Kushal_Chawda hi, proposal readings are good but all articles are about having multiple dates on fact table, and unified this dates with a common calendar... but I'm triying the get almost the opposite, from one unique date on the fact table create two different selectors that allows me to made diferent selections on the fact table without one be interfered by the other.