Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Custaccount | Invoicedate | Amount |
A | 01-04-2014 | 10000 |
A | 01-08-2014 | 50000 |
A | 02-04-2015 | 2600 |
A | 02-09-2015 | 45843 |
A | 10-09-2016 | 11465 |
B | 02-05-2016 | 55787 |
B | 03-05-2016 | 54411 |
C | 04-01-2018 | 5570 |
C | 05-01-2018 | 5688 |
C | 05-02-2018 | 55554 |
C | 09-12-2018 | 26898 |
D | 03-03-2019 | 456328 |
D | 04-03-2019 | 224698 |
D | 22-01-2018 | 5544 |
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 marked
Please help out... Thanks
May be this
Aggr(RangeSum(Above(Sum(Amount)), -Sum(Amount)), Custaccount, Quarter, (Year, (NUMERIC)))
May be this
Aggr(RangeSum(Above(Sum(Amount)), -Sum(Amount)), Custaccount, Quarter, (Year, (NUMERIC)))
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
Thanks You very much !!!
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
One more way of doing it with a proper Sort the year and quarter field
Before(total Sum(Amount),2) - Sum(Amount)
Hi Expert, can you try to explain the formula? How you construct it? Thanks!
Can you explain what exactly are you looking for?
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
This?
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