topic Re: Above() function- need help in QlikView

Above() function- need help

Wed, 27 Jan 2016 00:07:20 GMT

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

Re: Above() function- need help

Wed, 27 Jan 2016 00:09:08 GMT

=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

Wed, 27 Jan 2016 17:42:54 GMT

Anyone can help please?

Re: Above() function- need help

Anonymous — Wed, 27 Jan 2016 18:01:46 GMT

Could you send us a QVW sample?

Re: Above() function- need help

Wed, 27 Jan 2016 18:18:29 GMT

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

Re: Above() function- need help

Anonymous — Wed, 27 Jan 2016 18:37:14 GMT

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

Wed, 27 Jan 2016 18:51:36 GMT

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

Wed, 27 Jan 2016 18:57:22 GMT

Adding sample qvw

So where you see 24, I need 14.

Re: Above() function- need help

Oleg_Troyansky — Wed, 27 Jan 2016 19:15:28 GMT

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

Wed, 27 Jan 2016 21:38:53 GMT

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

Anonymous — Wed, 27 Jan 2016 23:46:39 GMT

Try this.

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

If(RowNo()=1,

Sum(sticky_count),

If(Sum(sticky_count)=0,

Sum(sticky_count)-

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

)

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