Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In Qlikview I could create a text box and enter the formula: sum(if(data_dt = max(data_dt), sales)) to get the sum of sales for the max date. When entering this formula in Qliksense I get an error, though don't know why. Is there a different formula definition? Is it ALL set analysis?
Thanks! Dan
I guess what object are your trying to do it in?
The "KPI" object.
that is strange... I would expect a text box expression to work in Qlik Sense's object. would you be able to share a sample where it isn't working?
1. in your if condition put in else part 0, as it you are taking sum() where if condition move to else part may causing the problem.. try this =sum(if(data_dt = max(data_dt), sales,0)).
2.also in your expression will work for a selection only right, when nothing selected and as u are written this in text box, the value of data_dt inside if for first statement will be what? eg if it is a column having 10 values which value it should be. check that..
It likes the code like this:
if(DATA_DT = Max(DATA_DT), Sum(Sub_Count),0)
However, to your 2nd point, the value is 0 unless there is a DATA_DT selected in a filter. I want this page to be static, and NOT use filters. So for all the measures, I want them to represent the max(DATA_DT). I can't get this to work. I created a variable in the script: Let vMaxDataDt = Max(DATA_DT). I'm not sure how to validate that this variable is "good". I tried using it in the formula above, replacing the "Max(DATA_DT", and it didn't like it. Any thoughts?
May be you are looking for this:
Sum(If(DATA_DT = Max(DATA_DT), Sub_Count))
Or
Sum({<DATA_DT = {"$(=Date(Max(DATA_DT)), 'YourDateFieldFormatHere)"}>} Sub_Count)
This should give you the max date sum for Sub_Count. This will change based on your selection. If you don't want it to change based on any selection Then make this small change:
Sum({<DATA_DT = {"$(=Date(Max({1}DATA_DT)), 'YourDateFieldFormatHere)"}>} Sub_Count)
Thanks for the suggestions, Sunny. Unfortunately it is still not working. I used the last expression you gave...the editor says the formula is "OK", but I'm still getting a value of 0 in the KPI object. Anything else I should be looking at or try?
Is DATA_DT a date field or timestamp field?
It is a date field.