10 Replies Latest reply: Jan 27, 2016 6:46 PM by Edgar Vazquez

# Above() function- need help

Hello,

I have rolling totals. I use above() function to get non rolling (just for that week) values. For e.g if week 1 has 5 count and week 2 has 10 (5+5 new) then I use the following expression to get just 5 for week 2. The logic fails when I have 0 for a week. e.g week 3. then in week 4 , I want to do week 4 count(cumulative) - week 2 count(cumulative) instead of week 4 count(cumulative) - week 3 count(cumulative). If several weeks in a row have 0 count I should be subtracting to last non zero count.

Sorry for being so confusing. Attaching the screenshot. A picture is worth a thousand words..

Thanks

DV

• ###### Re: Above() function- need help

=If(IsNull(IF(isnull(above([NT])) , [NT], \$(vExpAnnAggr(sticky_count))-above([NT]))) or IF(isnull(above([NT])) , [NT], \$(vExpAnnAggr(sticky_count))-above([NT])) < 0, 0, IF(isnull(above([NT])) , [NT], \$(vExpAnnAggr(sticky_count))-above([NT])))

where NT - =\$(vExpAnnAggr(sticky_count))

• ###### Re: Above() function- need help

Could you send us a QVW sample?

• ###### Re: Above() function- need help

Edgar, unfortunately I am not able to send qvw as it has sensitive info.

I have shown values on data points. If you notice 2nd data point, it says 10 but actually it is 10-5 = 5 (plotted at 5). this is a correct behavior. Any time the value hits 0, the next data point like 24 (shown in screenshot), it does 24- 0 = 24. This behavior is consistent with what is done in exp, but what i actually want is it should subtract the last non zero value. i.e 24-10 =14. I have used above to just go one step behind. I want to change that to a dynamic offset based on count of zeroes we have.

SET vExpAnnAggr = "aggr(sum(aggr(max({\$<metric_type={'daily_usage'}>} \$1),WeekStart,product_id)),WeekStart,user_id)"

NT = \$(vExpAnnAggr(sticky_count))

=If(IsNull(IF(isnull(above([NT])) , [NT], \$(vExpAnnAggr(sticky_count))-above([NT]))) or IF(isnull(above([NT])) , [NT], \$(vExpAnnAggr(sticky_count))-above([NT])) < 0, 0, IF(isnull(above([NT])) , [NT], \$(vExpAnnAggr(sticky_count))-above([NT])))

• ###### Re: Above() function- need help

ammmmm ok in this example:

Month,     Value

1               100

2               500

3               800

4             1000

5             1300

6             1700

7             2000

8             2000

9             2000

10           2200

11           2600

12           2800

___________________________________________

You wanna this result:

Month       Expression

1                 100

2                 400

3                 300

4                 200

5                 300

6                 400

7                 300

8                     0

9                     0

10                   0

11              400

12              200

I´m right???

• ###### Re: Above() function- need help

I was just putting a similar example for you to be able to help..

note changes in bold,

Month,     Value

1               100

2               500

3               800

4             1000

5             1300

6             1700

7             2000

8             0

9            0

10          0

11           2600

12           2800

___________________________________________

You wanna this result:

Month       Expression

1                 100

2                 400

3                 300

4                 200

5                 300

6                 400

7                 300

8                     0

9                     0

10                   0

11              600

12              200

• ###### Re: Above() function- need help

So where you see 24, I need 14.

• ###### Re: Above() function- need help

Hi Dhara,

I think the biggest logical problem that you are dealing with, is the fact that you have a running total that can possibly have zeros in the middle - that throws all the logic off.

So, first you need to calculate an intermediary expression RunnigBalance that replaces zeros with the value from Above().

Then, you can calculated your net change by subtracting the previous value of the RunningBalance from the current sum (and also, when the current sum is not zero).

See the solution in the attached document.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

• ###### Re: Above() function- need help

Thanks Oleg. Worked fine in sample chart. But did not work when I integrated with my qvw..I am making some mistake...

• ###### Re: Above() function- need help

Try this.

=======================================

If(RowNo()=1,

Sum(sticky_count),

If(Sum(sticky_count)=0,

0,

Sum(sticky_count)-

rangeMax( above( sum(sticky_count),1,rowno()-1))

)

)

=======================================