topic Common date filter referring two different date in App Development

Common date filter referring two different date

rob_vander — Mon, 30 Sep 2024 11:51:11 GMT

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?

Re: Common date filter referring two different date

Kushal_Chawda — Mon, 30 Sep 2024 12:18:02 GMT

@rob_vander which Qlik sense version are you on?

Re: Common date filter referring two different date

rob_vander — Mon, 30 Sep 2024 12:56:06 GMT

@Kushal_Chawda It's Nov 2023

Re: Common date filter referring two different date

Kushal_Chawda — Mon, 30 Sep 2024 15:13:03 GMT

@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

Re: Common date filter referring two different date

rob_vander — Mon, 30 Sep 2024 15:40:24 GMT

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

Re: Common date filter referring two different date

Kushal_Chawda — Mon, 30 Sep 2024 16:00:09 GMT

@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