Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sum for MaxDate

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.

qlik_sum.jpg

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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]);

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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

Gysbert_Wassenaar

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

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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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', ...

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand