topic Pivot Table - Running Total in QlikView

Pivot Table - Running Total

pkelly — Thu, 03 Sep 2009 21:45:04 GMT

Hi...

I have a pivot table with the following...

Dimension

Customer

Expression A

sum(IF(cam_FinQuarterYear = varBase AND cam_FinQuarterComplete = 'Yes' AND sin_SuperGroup='Core',sin_Sales,0))

Expression B

=sum(IF(cam_FinQuarterYear = varComparison AND cam_FinQuarterComplete = 'Yes' AND sin_SuperGroup='Core',sin_Sales,0))

I would like to add two further expressions..

Expresssion C

Running total of Expression B

Expresssion D

Based on running total..

Categorise top 50% as "A", next 30% as "B" and last 20% as "C"...

I did see a post which referred to "Total" but I cannot get my head around it - possibly too late in the day for me...

Any help greatly appreciated...

Pivot Table - Running Total

Thu, 03 Sep 2009 22:06:41 GMT

I have running sum in my pivot table using"

Sum(Expression B) + IF(rowno()=0,0,above(Cumm))

"Cumm" is the lable name I use for the expression that has the formula above in it.

Stephen

Pivot Table - Running Total

Thu, 03 Sep 2009 22:21:20 GMT

OK, for part two you need the following (I had the same type of issue when I had to create a pareto analysis)

if(Cumm/Rangesum(top(Expression B),1,noofrows()))>=.8.'C',if(Cumm/Rangesum(top(Expression B),1,noofrows()))>=.5.'B','A'))

Hope this helps.

Stephen

Pivot Table - Running Total

Thu, 03 Sep 2009 22:38:31 GMT

I think sjcharles expressions are shorter than mine, but I'll post mine anyway.

For the running total, I used (the expression is labelled Running):

If(Above("Running")>0, Above("Running") + Sum(Sales), Sum (Sales))

The ranking was crazy. I couldn't get Rank(TOTAL <Customer> Sum(Sales)) to work, even though it seems like it should. Rank allows the TOTAL <field> according to the tool tip. Here's my expression (the expression is labelled Ranking):

If(Aggr(Rank(Sum(Sales)), Customer) / Count(DISTINCT TOTAL Customer)>.5, 'A',
If(Aggr(Rank(Sum(Sales)), Customer) / Count(DISTINCT TOTAL Customer)>.3, 'B',
If(Aggr(Rank(Sum(Sales)), Customer) / Count(DISTINCT TOTAL Customer)>0, 'C', Above("Ranking"))))

I would probably set up a variable to hold:

Aggr(Rank(Sum(Sales)), Customer) / Count(DISTINCT TOTAL Customer)

Which gets you a more respectable:

If($(=vRank)>.5, 'A', If($(=vRank)>.3, 'B', If($(=vRank)>0, 'C', Above("Ranking"))))

That is definitely no fun on a pivot table.

Pivot Table - Running Total

pkelly — Thu, 03 Sep 2009 22:38:41 GMT

Thanks Stephen...

Based on your example, I have added the following Dimension which has the header "Cumm"

sum(IF(cam_FinQuarterYear = varComparison AND cam_FinQuarterComplete = 'Yes' AND sin_SuperGroup='Core',sin_Sales,0))

(rowno()=0,0,above(Cumm))

What it is doing for me is giving me a total at the bottom of the pivot table but I am not seeing a running total beside each customer.

This may be down to my explanation of what I am trying to achieve...

I am hoping to see...

A B C

Joe Bloggs 80 100 100

Jim SMith 75 150 250

With C being my running total...

Thanks in advance for any assistance you can give me...

Pivot Table - Running Total

Thu, 03 Sep 2009 23:00:05 GMT

I have attached a small example.

Stephen

Pivot Table - Running Total

pkelly — Fri, 04 Sep 2009 14:04:33 GMT

Thanks Stephen

Mine still isn't working properly and I think I may have found the reason why (although I am not sure why it is doing this)...

I added RowNo() to be both your example and mine as an expresssion...

In yours, it correctly reports the rows as being 1,2,3 etc...

On mine, it reports every row as being 1...

I cannot unfortunately post my file as it contains confidential company information and would take a while to reproduce using inline tables...

Any ideas?

Additional info...

I have created a new report for playing.

I have restricted the output so that I have fewer records.

If I filter on the pivot table it works okay.

If I clear the filter I get...

Allocated Memory Exceeded

I have tentatively increased my limits on the QlikView Server Management Console with no success.

Hope this helps someone solve my issue...

Pivot Table - Running Total

pkelly — Fri, 04 Sep 2009 17:19:16 GMT

Some more additional info...

I have been looking at other posts re memory and have found suggestions relating to flattening the tables...

In my model as it stands, I have three tables...

Calendar_Monthly, Customer and Sales Invoice

In my temp file I have flattened these so that I just have my Sales Invoice Table (I left joined the other two to this).

This has stopped the memory error but has left me with another issue as I am only left with transaction data.

I need to display all customers whether or not they have had sales or not but when I try to just flatten Sales Invoice and Calendar leaving me with Customer and Sales invoice I get the memory error (or sometime kicked out of QlikView)...

Again, any ideas greatly appreciated...