One table One mastercalender Date picker interference

[ElsKnockaert]

Good afternoon

I have a table in which I want to see how many prodcuts were registered per customer chain. Simple enough.

But I want 2 columns to compare the data between 2 selected date periods.

- Column 1: sum(realized) dependent on Cal_Year_Month

- Column 2: sum(realized) dependent on Cal_Year_Month but with a different value. 

  I've tried couple of things that I found on this forum but none seem

to work.

- Created Date Picker 2 from the MasterCalendar

Sum($(vAllowOnlyActivity)
          Aggr($(vAllowOnlyActivity)
               if( Min($(vAllowOnlyActivity)Activity_Last_Reg) = Min($(vAllowOnlyActivity){<Cal_Date = {"$(='<=' & Max(Picker_2_End))"}>}total<Cust_Id>Activity_Last_Reg), Activity_Real)

                 , Cust_Id,Prod_Id, Activity_Real )
         )

But the issue occurs when you try to activate the normal Cal_Year_Month to choose a period for column 1.

If you activate Cal_Year_Month, then in the column for Date 2, everything turns 0.

Sum($(vAllowOnlyActivity)
            Aggr($(vAllowOnlyActivity)
                   if( Min($(vAllowOnlyActivity)Activity_Last_Reg) = Min($(vAllowOnlyActivity)total<Cust_Id>Activity_Last_Reg), Activity_Real )
             , Cust_Id,Prod_Id, Activity_Real)
)

I've tried making a MasterCalender2 / making 2 date pickers / making a shadow table Activity_2 but nothing seems to bypass this interference.

Thank you in advance for any help!

[ogster1974]

Why don't you see if a date link table will help.

You have one master calendar

with a table between your calendar and fact table.

DateLink:

Load

%DateKey // Unique identifier of transaction in your fact table

'Date 1' as _DateType,

[Date 1] as "Date",

Resident FactTable;

Concatenate(DateLink)

Load

%DateKey

'Date 2' as _DateType,

[Date 2] as "Date",

Resident FactTable;

[ElsKnockaert]

Thank you for your reply, I've tried to do it like this and I have no error when loading.
However I'm not sure I did it correcty.
%Link_Prod_Cust_Empl_Date_Lang_Div is the unique identifier for my Activity table.
Activity_Reg_Date is the registration date of the product.
But my Activity table is also linked to my MasterCalendar by floor(Activity_Reg_Date) in %Link_Cal_Date.
And normally I use Cal_Date because that field is linked to  the other tables.

Is what I did correct here?

How do I use this in my expressions in the table? To compare one column working on Date1 to the adjacent column working on Date2?

If I try to make filters to separate Date1 and Date2, they are mutually exclusive.
I selected '2023 jan' for Date1 and as a result I can't choose a new date in Date2 to compare. 
If I can't get it to work in filters like this, it's not going to work in my table either probably :-s 

[ogster1974]

 was under the impression you had 2 dates date 1 and date 2 so was merging them together under one date.  from how you've implemented your code you only have one date. Activity Reg Date.

[ElsKnockaert]

Yes, in table Activity I have the date Activity_Reg_Date that is linked to MasterCalendar under its fields Cal_Date, Cal_Year_Month, Cal_Year, ... .

But I want to compare the sales of the product for example in 2023 January to the sales in 2022 January in one pivot table.

[ogster1974 · Accepted solution]

Right based on your last update I think I have a better idea of what you are looking for.  

https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/Visualizations/alter...

[ElsKnockaert]

I haven't the time to try it out today, or look into it, but this looks like what I need! I'll let you know if it worked!

Thank you so much!

[ElsKnockaert]

That did the trick! Thank you!!!