topic Re: rangesum(below) function and Pivot Chart - issue when selecting subs in QlikView

rangesum(below) function and Pivot Chart - issue when selecting subs

Thu, 17 Jul 2014 11:38:40 GMT

Dear Qlikview users,

I want to Report on daily net growth of a client database.

My source is: [id];[date];[event]
[event] = activation (a) or termination(t)

The output is in a Pivot Chart with dimension [date] with following expressions:

Activations:

sum( {$<EVENT={a}>} ID)

Terminations:

sum( {$<EVENT={t}>} ID)

Net growth per day:

sum( {$<EVENT={a}>} ID) - sum( {$<EVENT={t}>} ID)

Net growth cumulative:

rangesum (below(sum ({$<EVENT ={a}>} ID)-sum( {$< EVENT ={t}>} ID),1,NoOfRows(TOTAL)))

Untill now it works fine.

But the [Net growth cumulative] only works fast when having selected only a subset of [date] in the Pivot chart.

When I select all (with a correct sum of whole database) the chart becomes unusable because of very long load time (history is from years back)

But when selecting a subset then [Net growth cumulative] does only sums up the Activations and Terminations of days selected in the Pivot and therefore its incorrectly shown.

My questions: how to solve this problem?

I hope the solution should be in the NoOfRows(TOTAL) part ?

Note: it is important to be able to click and view in the charts based on ID level and not on aggregated level.

Or maybe this is just not possible and I should try another set up?

Thanks in advance for your thoughts, answers or solutions!

Jan

Re: rangesum(below) function and Pivot Chart - issue when selecting subs

michael_maeuser — Thu, 17 Jul 2014 11:41:52 GMT

try to replace noofrows(total) by your expression name

rangesum (below(sum ({$<EVENT ={a}>} ID)-sum( {$< EVENT ={t}>} ID),1,[NetGrowthCumulative]))

Generally I would recommend to load your table in script resident and do a rangesum here, because this expression is mostly imperformant

Re: rangesum(below) function and Pivot Chart - issue when selecting subs

Fri, 18 Jul 2014 12:10:50 GMT

Dear Michael,

Thank you for your reply but this doesn't work.

I agree with your solutions using a load resident script.

But in the load script you need to use another function as below?

I tried something with Peek.

This is my script now:

As you see I aggregated some fields.

So sum(ACT)-sum(TERM)-sum(OUTP) defines my NET growth per Date.

The field AccumulatedSum does not give me results, so there must be something wrong.

Probably with this part of Peek(AccumulatedSum) ?

LOAD
Date,
sum(ACT) as ACT,
sum(TERM) as TERM,
sum(OUTP) as OUTP,
sum(SUB) as SUB,
sum(ACT)-sum(TERM)-sum(OUTP) as NET,
If(Previous(Date) <> Date, (sum(ACT)-sum(TERM)-sum(OUTP)), (sum(ACT)-sum(TERM)-sum(OUTP)) + Peek(AccumulatedSum)) AS AccumulatedSum
Resident TABLE1
GROUP BY
Date
ORDER BY Date ASC;