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: 
Anonymous
Not applicable

Compare 2 quarters of different years in multiple years

Hello Experts

I need to compare results of each quarter + year to its previous quarter + year. I tried different options suggested on community but it didn't helped as i have customer and other dimension to it. here is the data.

CustaccountInvoicedateAmount
A01-04-201410000
A01-08-201450000
A02-04-20152600
A02-09-201545843
A10-09-201611465
B02-05-201655787
B03-05-201654411
C04-01-20185570
C05-01-20185688
C05-02-201855554
C09-12-201826898
D03-03-2019456328
D04-03-2019224698
D22-01-20185544

2020-02-13_15h09_26.png

I got above results with Pivot Table. For me the next step is to get the difference of each quarter compared with earlier year quarter like below--- yellow marked2020-02-13_15h16_23.png

 

Please help out... Thanks

1 Solution

Accepted Solutions
sunny_talwar

May be this

Aggr(RangeSum(Above(Sum(Amount)), -Sum(Amount)), Custaccount, Quarter, (Year, (NUMERIC)))

View solution in original post

11 Replies
sunny_talwar

May be this

Aggr(RangeSum(Above(Sum(Amount)), -Sum(Amount)), Custaccount, Quarter, (Year, (NUMERIC)))
Anonymous
Not applicable
Author

Once again proved, you are genius...:)

That worked perfectly, only one thing, if no value in previous quarter it goes one more previous level.

But for me that is logical when you are comparing results to previous year/quarters

 
 

2020-02-14_10h26_31.png

Thanks You very much !!!

 

Anonymous
Not applicable
Author

Hello Sunny, 

can we add the sum of columns year-wise now? The number of years are random and will increase in future so don't have fixed column count

Thanks

qliksus
Specialist II
Specialist II

One more way of doing it with a proper Sort the year and quarter field 

 

Before(total Sum(Amount),2) - Sum(Amount)

alex00321
Creator II
Creator II

Hi Expert, can you try to explain the formula? How you construct it? Thanks!

sunny_talwar

Can you explain what exactly are you looking for?

Anonymous
Not applicable
Author

Hello,

I an trying to have a column with sum of 4 quarters after each year (which is the total for year)  for amount column.

Thanks

sunny_talwar

This?

image.png

Anonymous
Not applicable
Author

Yes, That's it. I tried same partial sum but was trying on YEAR instead of Quarter.. This works perfect... Thanks

Any way we can avoid showing sum of column "Diff"?

 

thanks,

Shrikant