Above() function- need help

[#1]

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

[#2]

=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))

[#3]

Anyone can help please?

[Anonymous]

Could you send us a QVW sample?

[#5]

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

Following is some more information if that helps..

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])))

[Anonymous]

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

[#7]

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

[#8]

Adding sample qvw

So where you see 24, I need 14.

[Oleg_Troyansky · Accepted solution]

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!

[#10]

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