Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Gurus,
I have an example with dummy sales data where in i need to plot time series Quarterly sales comparison.
I want to derive comparison table that holds the sales with max quarters for each combination.
*** Each combination (Company, Route) need not have the data for all quarters. Trick is the expression should pick latest quarter wherever data is present.
Refer attached png for more details
A | Latest Qtr Sales | Previous Qtr Sales | Latest Qtr of previuos year | Q/Q | Y/Y | |
Retail | 250 | 150 | 290 | = (250 - 150)/150 | = (250 - 290)/290 | |
wholesale | 150 | 180 | 140 | = (150 - 140)/140 | = (150 - 180)/180 | |
B | ||||||
Retail | 150 | 120 | 260 | = (150 - 120)/120 | = (150 - 260)/260 | |
wholesale | 200 | 150 | 180 | = (200 - 150)/150 | = (200 - 180)/180 |
PFA the image and the attachment
Check the attached
How to make these expressions work even if I select on any qtr.. where to FYQTR =,
In the proposed solution, bottom table becomes blank for many columns if you select any quarter (Ex: 2001 - Q4) from the top Table..
I am trying to make your solution more dynamic...
How to changes to be done to get partial sum in the bottom table (tried with Presentation Tab) but the values are different..
Does this look right?
Kudos Sunny!
Yippe!!! Numbers are correct, any chances of getting Partial Sum in the below table..
Will you be able to share the app?
PFA the image and the attachment
Kudos Sunny!
sorry for reopening the query.
This solution works fine. Got another query.
How to get the values from latest quarter irrespective of whether the data is available or not?
what changes to be done in expression?
Sum(Aggr(
FirstSortedValue({<FYQTR, YearMonth = {"$(='<=' & Max(YearMonth))"}>} Aggr(Sum({<FYQTR, YearMonth = {"$(='<=' & Max(YearMonth))"}>}Sales), Company, Route, YearMonth), -Aggr(Only({<FYQTR, YearMonth = {"$(='<=' & Max(YearMonth))"}>}YearMonth), Company, Route, YearMonth)), Company, Route))
I want to understand how this query is built, tried to break into segments to understand, but unable to crack it.
Please create a new thread for this.
Best,
Sunny