Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master III
Master III

In the same Column,

see attachment

View solution in original post

10 Replies
antoniotiman
Master III
Master III

Hi Kosmas,

see attachment.

Regards,

Antonio

Not applicable
Author

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
Master III
Master III

You select Day from 2 to 4.

antoniotiman
Master III
Master III

In the same Column,

see attachment

Not applicable
Author

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
Master III
Master III

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
Author

Hello Antonio,

Could you please explain the philosophy of the solution ?

Thanks

Kosmas

antoniotiman
Master III
Master III

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
Author

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