Time series Calcs

[narayanamsn]

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

[sunny_talwar]

Check the attached

[narayanamsn]

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..

[sunny_talwar]

Does this look right?

[narayanamsn]

Kudos Sunny!

Yippe!!! Numbers are correct, any chances of getting Partial Sum in the below table..

Will you be able to share the app?

[sunny_talwar · Accepted solution]

PFA the image and the attachment

[narayanamsn]

Kudos Sunny!

[narayanamsn]

stalwar1‌

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.

[sunny_talwar]

Please create a new thread for this.

Best,

Sunny

[narayanamsn]

Thanks Sunny, Created a thread.

Re: Time comparison

Regards,

Satya