topic Re: Can't get the right value with Set analysis in QlikView

Can't get the right value with Set analysis

Derek_T — Mon, 07 Nov 2022 19:55:08 GMT

I have a measure that tracks the number of aircrafts in service each month. I need the value for the last month of the current selection to compute the KPI for the current month.

I have a measure that tracks the number of AC/month, see graph below:
=SUM(aircraft_in_operation)

The column "aircraft_in_operation" contains float between 0 and 1 based on when the AC went into service (15th of the month => 0.5)

It works fine, and the value for the last month is around 160.

Now, I've tried this set analysis to get only the value for the last month (August 2022):
SUM({$<reportingDate = {">=$(=MonthStart(Max(EVENT_DATE)))"}>}aircraft_in_operation)

"EVENT_DATE" is from my calendar table, it's connected to the table with "reportingDate " and "aircraft_in_operation" by a key (month-operator-msn).

In the expression, {=MonthStart(Max(EVENT_DATE))} returns the correct value of "8/1/2022", so I should get the sum just for August, which is 160 but I get a number that's +5000.

What am I missing ?

Re: Can't get the right value with Set analysis

ckarras22 — Mon, 07 Nov 2022 23:47:56 GMT

Hello,

Can you confirm that AUG2022 is the last month in the table where reportingDate resides?

Does reportingDate has the same format as the "8/1/2022" in its values?

Re: Can't get the right value with Set analysis

Derek_T — Tue, 08 Nov 2022 15:15:14 GMT

Thanks, yes date format checks out.

The only solution I found was to store the value returned by: {=MonthStart(Max(EVENT_DATE))} in a variable. And then use this variable in the expression.

I don't understand why it worked using variables and not the other way though.