Common date filter referring two different date

[rob_vander]

Hi All,

I want to create a report with MonthYear as dimension but KPI referring to two different date. I have existing data model with OrderDate & ShippingDate where calendar is linked to OrderDate. Below is the sample dataset

OrderID	OrderDate	ShipDate
1	15/01/2024	19/01/2024
2	19/01/2024	20/01/2024
3	20/01/2024	02/02/2024
4	10/02/2024	12/02/2024
5	15/03/2024	18/03/2024

Below is the required output

MonthYear	Count of Orders	Count of Shipment
Jan-24	3	2
Feb-24	1	2
Mar-24	1	1

I would create Canonical date in my model to handle this scenario, but issue is that I am not allowed to change the model due to critical app. Hence, I would like to somehow manage it through chart expression. Is it achievable using chart expression?

[Kushal_Chawda]

@rob_vander  which Qlik sense version are you on?

[rob_vander]

@Kushal_Chawda  It's Nov 2023

[Kushal_Chawda · Accepted solution]

@rob_vander  Assuming that your MonthYear field is numeric field created either using date(monthstart(OrderDate),'MMM YYYY')  or monthname(OrderDate). 

You can utilise chart level scripting feature of Qlik. You need to enable chart scripting option here

Once char scripting option is enable create table with Dimension Month and below two measures

1) Count(distinct OrderID)

2) Sum(0)

In Scripts section of table, add below script

//Gets the total no of rows in the current Chart or Table

Let r = HCNoRows();

//Looping through the rows to generate the Count

For i = 1 to r

//Picks the value of the Month dimension to create monthstart (vMaxDate) and monthend(vMaxDate)  date and store it in variables

   Let vMinDate = monthstart(HCValue(#hc1.dimension.1, i));
   Let vMaxDate = monthend(HCValue(#hc1.dimension.1, i));

// Using above variable calculate measure referencing Ship date and store the value in vQuantityDelivered variable

   Let vCountShipment = $(=count({<ShipDate={">=$(vMinDate)<=$(vMaxDate)"}>} distinct OrderID));

 //Pushing the computed value to the Count of Shipments measure 

   Put #hc1.measure.2(i) = $(vCountShipment);
   
next

[rob_vander]

@Kushal_Chawda  This is good option. I never tried chart level scripting before. Is it safe to use?  But one thing I noticed is chart total shows 0 for Count of shipment. Do you know how to fix it?

[Kushal_Chawda · Accepted solution]

@rob_vander  It seems some limitation of this feature. May be @Michael_Tarallo @Patric_Nordstrom can highlight further if it is known issue.

But there is a workaround. Create variable vCountShipmentTotal below on frond end

=count({<ShipDate={">=$(=monthstart(min(OrderDate)))<=$(=monthend(max(OrderDate)))"}>} distinct OrderID)

Now, Instead of sum(0) expression, call vCountShipmentTotal variable in second measure

Note: Make sure that variable name is different than used in chart Script