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

[girishsrini]

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

[hic]

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

[girishsrini]

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.

[juraj_misina]

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

[girishsrini]

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

[juraj_misina]

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

[juraj_misina · Accepted solution]

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

[girishsrini]

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

[juraj_misina]

Hello Girish,

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

[girishsrini]

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.