topic Re: Two date selector for the fact table in App Development

Two date selector for the fact table

adecora — Fri, 18 Oct 2024 11:23:20 GMT

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

Re: Two date selector for the fact table

marksouzacosta — Fri, 18 Oct 2024 13:31:02 GMT

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

Re: Two date selector for the fact table

Kushal_Chawda — Fri, 18 Oct 2024 15:19:43 GMT

@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

Re: Two date selector for the fact table

Vegar — Sat, 19 Oct 2024 06:29:17 GMT

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

Re: Two date selector for the fact table

adecora — Tue, 22 Oct 2024 11:41:57 GMT

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

Re: Two date selector for the fact table

adecora — Tue, 22 Oct 2024 11:51:50 GMT

@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.