topic Re: Sum for MaxDate in QlikView

Sum for MaxDate

Anonymous — Thu, 20 Sep 2018 11:50:33 GMT

Hi everyone,

i have the same problem like in this thread: Show sales value for maximum date

Altough I'm using the same syntax my result is 0. I defined a variable MaxDate which shows the selected Date correctly. But in the sum if statement there's no result. When i switch MaxDate to '2018/09/20' it works fine. Thanks in advance for help.

Re: Sum for MaxDate

Gysbert_Wassenaar — Thu, 20 Sep 2018 16:43:50 GMT

Try sum({<Berichtsdatum={'$(MaxDate)'}>} [offener errechneter Auftragswert AB])

Re: Sum for MaxDate

Anonymous — Mon, 24 Sep 2018 15:39:41 GMT

Hi Gyspert, my result is still 0. I made a new app to solve this issue but still the same result. It works when i switch ... {'$(DateMax)'}... to '2018/09/21' for example and if I take DateMax as a stand alone Measure it is displayed correctly.

LOAD

[offener errechneter Auftragswert AB],

[Berichtsdatum]

FROM [lib://AttachedFiles/ABÜ_täglich.xlsx]

(ooxml, embedded labels, table is ABUE);

Set DateMax = Max( [Berichtsdatum] );

Set AB_akt = sum({<Berichtsdatum={'$(DateMax)'}>} [offener errechneter Auftragswert AB]);

Re: Sum for MaxDate

Gysbert_Wassenaar — Tue, 25 Sep 2018 06:38:28 GMT

Max(Berichtsdatum) will return a number, not a formatted date. Try SET DateMax = Date(Max(Berichtsdatum),'YYYY/MM/DD');

Re: Sum for MaxDate

Anonymous — Tue, 25 Sep 2018 09:25:52 GMT

Sorry, result is still the same. But thanks for your help.

Re: Sum for MaxDate

Gysbert_Wassenaar — Tue, 25 Sep 2018 09:50:31 GMT

Try SET DateMax = =Date(Max(Berichtsdatum),'YYYY/MM/DD');

Or LET DateMax = '=Date(Max(Berichtsdatum),'YYYY/MM/DD')';

Re: Sum for MaxDate

Anonymous — Tue, 25 Sep 2018 10:52:08 GMT

With SET DateMax = =Date(Max(Berichtsdatum),'YYYY/MM/DD'); I get the result 9,34 for $DateMax and now Set AB_akt = sum({<Berichtsdatum={'$(DateMax)'}>} [offener errechneter Auftragswert AB]); shows the sum without the aggregation for $DateMax

With LET DateMax = '=Date(Max(Berichtsdatum),'YYYY/MM/DD')'; i get a failure message:

Unexpected token: 'YYYY', expected one of: 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', 'and', ...

Re: Sum for MaxDate

Gysbert_Wassenaar — Tue, 25 Sep 2018 11:28:03 GMT

Oops, yes that LET won't work with nested quotes.

9,34 = 2018/9/24. That means that the variable contains the correct value. If you use =$(MaxDate) in a text object it will be evaluated as an expression. But if you use it in a set analysis expression in quotes then '$(MaxDate)' then it will work ok. Just as when you use ='$(vMaxDate)' in a text object.