topic Re: Expression differences between Qlikview & Qliksense (#1) in App Development

Expression differences between Qlikview & Qliksense (#1)

Tue, 12 Jan 2016 23:29:40 GMT

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

Re: Expression differences between Qlikview & Qliksense (#1)

sunny_talwar — Tue, 12 Jan 2016 23:37:58 GMT

‌I guess what object are your trying to do it in?

Re: Expression differences between Qlikview & Qliksense (#1)

Tue, 12 Jan 2016 23:40:37 GMT

The "KPI" object.

Re: Expression differences between Qlikview & Qliksense (#1)

sunny_talwar — Tue, 12 Jan 2016 23:50:45 GMT

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

Re: Expression differences between Qlikview & Qliksense (#1)

vinay_hg — Wed, 13 Jan 2016 06:29:21 GMT

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

Re: Expression differences between Qlikview & Qliksense (#1)

Thu, 14 Jan 2016 23:17:12 GMT

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?

Re: Expression differences between Qlikview & Qliksense (#1)

sunny_talwar — Thu, 14 Jan 2016 23:25:44 GMT

May be you are looking for this:

Sum(If(DATA_DT = Max(DATA_DT), Sub_Count))

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)

Re: Expression differences between Qlikview & Qliksense (#1)

Fri, 15 Jan 2016 17:58:00 GMT

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?

Re: Expression differences between Qlikview & Qliksense (#1)

sunny_talwar — Fri, 15 Jan 2016 17:59:52 GMT

Is DATA_DT a date field or timestamp field?

Re: Expression differences between Qlikview & Qliksense (#1)

Fri, 15 Jan 2016 18:04:42 GMT

It is a date field.

Re: Expression differences between Qlikview & Qliksense (#1)

sunny_talwar — Fri, 15 Jan 2016 18:06:07 GMT

So if you do Num(DATA_DT) you see an integer and not something like 45169.1532564 kind of a number? Just want to make sure we are confident that DATA_DT is a date field and not timestamp.

Re: Expression differences between Qlikview & Qliksense (#1)

Fri, 15 Jan 2016 18:09:08 GMT

Yes...when I do Num(DATA_DT) I see 42369

Re: Expression differences between Qlikview & Qliksense (#1)

sunny_talwar — Fri, 15 Jan 2016 18:18:54 GMT

Not sure what isn't working. Would you be able to share a sample?

Re: Expression differences between Qlikview & Qliksense (#1)

reddy-s — Fri, 15 Jan 2016 20:30:56 GMT

Hi Dan,

Sounds Strange! The expressions should work the same in Qlikview and QlikSense. As Sunny Suggested, it would be easy if you share a QVF.

Thanks,

Sangram Reddy.

Re: Expression differences between Qlikview & Qliksense (#1)

Mon, 18 Jan 2016 19:30:33 GMT

I found a/the solution when looking through the documentation. I'm not sure why my original formula worked in Qlikview but not Qliksense, but it needed a "Max" in there, as well as a "TOTAL"....

Sum(If(DATA_DT=Max(TOTAL DATA_DT), Sales))

This gives me the total sales, without a filter, for the max DATA_DT. Thanks for all the help.