Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

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

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

Tags (1)
2 Replies
michael_maeuser
Contributor III

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

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

Not applicable

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

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;