topic Re: Above() in QlikSense is not working for the same criteria beyond once in App Development

Above() in QlikSense is not working for the same criteria beyond once

girishsrini — Thu, 11 Jan 2018 20:07:15 GMT

Hi,

Good-Day.

There is a challenge in populating the previous value continuously beyond once using Above() function. I have attached a sample QVF file which illustrates the problem.

Requirement:

The “Sales Goal-USA” column is calculated based on the “Yearly Sales Goal” – cumulative “Actual Sales” as of previous week divided by total number of week in year. The value in “Sales Goal-USA” is always looks at previous week’s value. When there is no “Actual Sales” in the previous week, The value in the “Sales Goal-USA” column should pick previous week’s “Sales Goal-USA” value.

Calculation is working fine except if there are repeated 0 in “Actual Sales” column.

Here are the calculations used.

Sales Goal - USA

IF(Country='USA' AND [Pending Sale]=0 AND Above(Alt([Actual Sale],0),1)=0,

Above($(v_Weekly Sales Goal-USA),1),

IF(Country='USA' AND [Pending Sale]=0 AND Alt([Actual Sale],0)=0,

$(v_Weekly Sales Goal-USA),

IF(Country='USA' AND [Pending Sale]=0 AND Alt([Actual Sale],0)>0,

$(v_Weekly Sales Goal-USA),

Sum([Pending Sale]))))

$(v_Weekly Sales Goal-USA)

IF(Country='USA' AND [Pending Sale]=0,

(($(v_Country Yearly Goal-USA) - Alt(((rangesum(Above(TOTAL Alt([Actual Sale],0),0, rowno(TOTAL))))-Alt([Actual Sale],0)),0))/(54-$(v_RowNo_Sequence))),

Sum([Pending Sale]))

$(v_Country Yearly Goal-USA)

Sum({$<Sale_Country={'USA'}>}Goal_Value)

Filter Selection in dashboard – Year: 2017, Country: USA

Note: Week 13 should be picking 1872 Similarly Week 48 should be picking 4750 for rest of the weeks as there is no “Actual Sales”

What could be wrong in the script, is there anything missing in Above function?

Have been attempting possible ways since few days, However no solution yet. Please help.

Regards,

Girish

Re: Above() in QlikSense is not working for the same criteria beyond once

hic — Fri, 12 Jan 2018 11:06:51 GMT

The reason your expression doesn't work is that you have several naked field references in them, and they cannot be evaluated. For instance Country='USA' cannot be evaluated when your only dimension is Week. Country will be evaluated as NULL.

You need to wrap these in an an aggregation function, e.g. MinString(Country)='USA'. If you want to test whether USA is one of several countries you can use Index(Concat(distinct Country, ','),'USA')>0.

See more on Use Aggregation Functions!

HIC

Re: Above() in QlikSense is not working for the same criteria beyond once

girishsrini — Fri, 12 Jan 2018 15:13:23 GMT

Thanks Henric for the inputs.

I did wrap the column within the aggregation as mentioned, however there is no change in the report output. Am I missing anything else.. Pls. suggest.

Re: Above() in QlikSense is not working for the same criteria beyond once

juraj_misina — Fri, 12 Jan 2018 16:30:56 GMT

Hi Girish,

I guess this should work for you:

IF(Country='USA' AND [Pending Sales]=0 AND Above(Alt([Actual Sales],0),1)=0,

Above([Sales Goal - USA],1),

IF(Country='USA' AND [Pending Sales]=0 AND Alt([Actual Sales],0)=0,

$(v_Weekly Sales Goal-USA),

IF(Country='USA' AND [Pending Sales]=0 AND Alt([Actual Sales],0)>0,

$(v_Weekly Sales Goal-USA),

Sum([Pending Sale]))))

Best

Juraj

Re: Above() in QlikSense is not working for the same criteria beyond once

girishsrini — Sat, 13 Jan 2018 19:04:18 GMT

Hello Juraj,

Thank you.

There is an improvement from your suggestion. Now, the second consecutive 0 is picking previous goal But, from the 3rd consecutive 0, the previous goal is not picking.

Please check the screenshot attached.

Regards,

Girish

Re: Above() in QlikSense is not working for the same criteria beyond once

juraj_misina — Sat, 13 Jan 2018 22:05:27 GMT

Hello Girish,

I think I also might have changed field names to measure labels in the variables used, but I did not mention it in my post. However I can't double check now. Let me come back to you on Monday (or try changing the variables and see what happens).

Best

Juraj

Re: Above() in QlikSense is not working for the same criteria beyond once

juraj_misina — Mon, 15 Jan 2018 08:34:57 GMT

Hello Girish,

so I double checked my file and yes, I did also change the variable v_Weekly Sales Goal-USA:

IF(Country='USA' AND [Pending Sales]=0,
(($(v_Country Yearly Goal-USA) - Alt(((rangesum(Above(TOTAL Alt([Actual Sales],0),0, rowno(TOTAL))))-Alt([Actual Sales],0)),0))/(54-$(v_RowNo_Sequence))),
Sum([Pending Sale]))

Hope this helps.

Juraj

Re: Above() in QlikSense is not working for the same criteria beyond once

girishsrini — Mon, 15 Jan 2018 16:43:53 GMT

Hi Juraj,

Thank you so much for putting time to get a solution.

I can't find any difference in the output after your expression.

Please send the app that you have verified so that i can check.

My email id: Girish.srinivasaiah@hotmail.com

Regards,

Girish

Re: Above() in QlikSense is not working for the same criteria beyond once

juraj_misina — Mon, 15 Jan 2018 20:12:06 GMT

Hello Girish,

the app is attached to my previous post. The expression worked for me:

Re: Above() in QlikSense is not working for the same criteria beyond once

girishsrini — Wed, 17 Jan 2018 10:23:39 GMT

Hi Juraj,

Greetings.

It worked in my app.

Thank you so much for the solution provided. Din't know that we can loop in same column within the column expression in QlikSense.

Appreciate your time in helping on this.

Please check, if we see the Total for the Column "Sales Goal - USA" it shows 58400 but when exported to excel for manual data validation... I see 175367. The total shows wrong in tabular output.

Re: Above() in QlikSense is not working for the same criteria beyond once

juraj_misina — Wed, 17 Jan 2018 11:26:48 GMT

Hi,

I guess this should be easily fixed by setting the "Totals function" setting in measure properties to Sum.

Juraj

Re: Above() in QlikSense is not working for the same criteria beyond once

girishsrini — Wed, 17 Jan 2018 12:35:26 GMT

Juraj,

Great. Simple yet effective. Because we were aggregating at the expression, left it to Auto.

It worked. thanks again.