Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to calculate the difference of sum in a PivotTable for specific values of one dimension

Hi all,

I have one Table with : Product, Day , Quantity

The Pivot Table has as dimensions Product , Day with sum(Quantity) as Expression.

How could I calculate the difference of the sum per product between e.g. Day 1 - Day7 , Day 6 - Day 4

Product Day      1        2     ....       7

A                    90     100     ----     75     To calculate 90-75 = 15 (The difference of the sum of Day 7 and Day 1 per product)

B                    92                 ----     82      92-82=10

C                    95                 ----     46      95-46=49

Thanks a lot

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: How to calculate the difference of sum in a PivotTable for specific values of one dimension

In the same Column,

see attachment

10 Replies
antoniotiman
Honored Contributor III

Re: How to calculate the difference of sum in a PivotTable for specific values of one dimension

Hi Kosmas,

see attachment.

Regards,

Antonio

Not applicable

Re: How to calculate the difference of sum in a PivotTable for specific values of one dimension

Hi Antonio,

Thank you for the answer.

If I go one step more and add also the difference between  Day 2 - Day4.

Could you please specify how would calculate both ?

Grazie

Kosmas

antoniotiman
Honored Contributor III

Re: How to calculate the difference of sum in a PivotTable for specific values of one dimension

You select Day from 2 to 4.

antoniotiman
Honored Contributor III

Re: How to calculate the difference of sum in a PivotTable for specific values of one dimension

In the same Column,

see attachment

Not applicable

Re: How to calculate the difference of sum in a PivotTable for specific values of one dimension

Hi Antonio,

The way you used min and max makes dynamic the case. This is very good.

Sum({<Day={"$(=Min(Day))"}>} Qty) -Sum({<Day={"$(=Max(Day))"}>} Qty)

In the second file you include the Day 2 - Day4 as a concatenation in Expression 1.

If you separate this concatenation in an another new expression, what should I take care of to be correct ?

Grazie mille

Kosmas

antoniotiman
Honored Contributor III

Re: How to calculate the difference of sum in a PivotTable for specific values of one dimension

The column of concatenation is a TOTAL column.

You don't have two TOTAL.

However You can split Concatenation in 2 rows changing &'        '& to &Chr(10)&.

If You want dynamic Days (4), You change Min(Day) and Max(Day) -> Min(Day,1) Min(Day,2) Min(Day,3) Min(Day,4)

Regards,

Antonio

Not applicable

Re: How to calculate the difference of sum in a PivotTable for specific values of one dimension

Hello Antonio,

Could you please explain the philosophy of the solution ?

Thanks

Kosmas

antoniotiman
Honored Contributor III

Re: How to calculate the difference of sum in a PivotTable for specific values of one dimension

Hi Kosmas,

If You have 2 Interval (4 days) e.g 2-3 and 4-6

You can select in (Ctrl + Click).

In Expression Min(Days,1) is the First, Min(Days,2) the 2nd and so on.

You Replace Min(..) in  TOTAL Expression.

If You want to split TOTAL in 2 rows, You replace &'   '& with &Chr(10)&.

(In Grafic panel Text Cells = 2 rows).

Regards,

Antonio

Not applicable

Re: How to calculate the difference of sum in a PivotTable for specific values of one dimension

Can I have more than one column for the Total ?

One having the 2-3 and the next column having 4-6, instead of having them concatenated.

Grazie

Kosmas

Community Browser