18 Replies Latest reply: Jan 26, 2018 9:03 AM by vikas mahajan

# Year over year comparison

Hi,

I would like create a chart that shows sales dynamic year over year within year dimension or month dimension.

It's quite common as I expect, but I've searched through the resources i.e. Set Analysis for certain Point in Time

and I've tried to implement it to my model but I didn't manage.

This is the formula I've created to build year over year within year dimension

Sum({<Category={"Sales"}>} Amount)/

Sum({<Category={"Sales"},Year=\${"Year-1"}>} Amount)

I've tried many combinations, but I didn't find the proper solution.

What is more if I want to have the formula working correctly within month dimension which means I would like to have my values ie. dived by 2016 - May over 2015 - May, this is something I'm unable to overcome currently.

• ###### Re: Year over year comparison

Hi

if you have Month data like MMM-YYYY (May-2016)  as MonthYear , you can try this

Current month (last year)

Current month (this year)

Sum({\$<[MonthYear]={"\$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>} Amount )

try this ..

Sum({\$<[MonthYear]={"\$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>} Amount ) /

• ###### Re: Year over year comparison

Thanks, I've added this date format to my MasterCalendar

like that   Month(TempDate)&'-'&Year(TempDate)  AS MonthYear

and then I've added this formula - exactly as Yours but with additional Category filter but it didn't work.

Sum({\$<[MonthYear]={"\$(=Date(Max(MonthYear), 'MMM-YYYY'))"},Category={"Sales"}>} Amount) /

• ###### Re: Year over year comparison

Hi

1)Is your  Monthyear filed in MMM-YYYY format ?

2)what output is coming when apply only one year  like following ..?

Sum({\$<[MonthYear]={"\$(=Date(Max(MonthYear), 'MMM-YYYY'))"},Category={"Sales"}>} Amount)

• ###### Re: Year over year comparison

1) Yes

2) It gives 0 as a result.

• ###### Re: Year over year comparison

This is the effect of MMM-YYYY joining in MasterCalendar. When I sort it, it is sorted like it was a text, because "cze"  in Polish means "jun" in English. (below)

Although I've used in another chart that kind of variable.

It gives as a result Sales for the previous year correctly, but when I try to use it

something like that, it gives me for some variants good results, but when I analyse data with yearly dimension it is not good.

Sum({\$<Category={"Sales"}>}Amount)/

• ###### Re: Year over year comparison

Create a MonthYear field like below

DATE(MonthStart(DocumentDate,'MMM-YYYY')) as MonthYear

Expression for Previous Year Same Month

• ###### Re: Year over year comparison

If I want to generate the date like You suggested

DATE(MonthStart(DocumentDate,'MMM-YYYY')) as MonthYear

I have to change "DocumentDate" into "TempDate" from my Master Calendar. But it doesn't show any results - this dimension is without any data.

However in Your second formula MonthYear is compared with the format YYYYMM which is not the format for MonthYear = MMM-YYYY.

If I compare it with the dimension I've created with the format YYYYMM it still doesn't work properly.

It works fine for months when I use that kind of formula like below. However it's not good when I want to analyze data over Year dimension which is the problem for now.

Sum({\$<Category={"Sales"}>}Amount)/

>}Amount)

where

varMonthMax=Num(Month(max(DocumentDate)))

• ###### Re: Year over year comparison

Still no idea, I can handle with the issue comparing year over year when in dimension on X - axis is month. However it doesn't work when I need to have Year there. Suggestions above didn't work for me or I implemented them wrong.

I would like to have values in my chart that could show my Year over Year Sales dynamics.

• ###### Re: Year over year comparison

Can you please share ur app, or app with sample data and where the logic is not working?

• ###### Re: Year over year comparison

Hi,

Thank you for your reply. This is my sample app. There is no problem for YoY comparison over months, by over Year dimension it doesn;t work.

Maciek

• ###### Re: Year over year comparison

Hi, For year on year, You want to compare the current yr/last yr numbers rite?. i have added the logic for u in the app. Please check and let me know.

• ###### Re: Year over year comparison

Hi,

Yes, I would like to compare the year with the previous year. But I would like to have it compared in one bar chart and in percent.

Your app works fine if I analyze year over year but with months

If I divide it, it is the format required (one series):

Sum({\$<Category={"Sales"},Year={"\$(=max(Year))"}>}Amount)

/

Sum({\$<Category={"Sales"},Year={"\$(=max(Year)-1)"}>}Amount).

However I would like to get more complex result as well: for year:

So on my X axis If I have:

Year 2016 I would like to have value on Y axis "Sales 2016/Sales 2015"

Year 2015 I would like to have value on Y axis "Sales 2015/Sales 2014"

Year 2014 I would like to have value on Y axis "Sales 2014/Sales 2013" (in the database there is no data for 2013 so it would result in 0)

Year 2013 I would like to have value on Y axis "Sales 2013/Sales 2012" (in the database there is no data for 2012 so it would result in 0)

Just like on the screen below.

Is it feasible?

• ###### Re: Year over year comparison

Hi, I'm afraid it is not possible as set analysis is done considering the dimensions. There is not direct way to do the above requirement.

• ###### Re: Year over year comparison

Hi,

Thats a pity, ok. Thank you for your time and your help.

Maciek

• ###### Re: Year over year comparison

Because I couldn't get the effect with set analysis I've created an additional View using SQL.

I have moved bacward the period in the table where is the structure of the company and that is hov I get the desired effect. It's not ideal but it works fine for me.

Maciek

• ###### Re: Year over year comparison

Hi

Can you please attach sample QVF i have same requirement

Vikas

• ###### Re: Year over year comparison

Hi Maciej

Is this the only way to achieve result? I think some combination of variables and TOTAL tag in expression is the right way.

Przemysław Staniszewski

• ###### Re: Year over year comparison

Hi Przemek,

I believe it's not the only way to do this. However, I have put a lot of effort to achieve it and I've failed to get satisfactory results. In my opinion using TOTAL is not the direction I would search for the solution of the problem. As I understand TOTAL, it would bring the total sum of the measure, while I would like to compare values from the specific period to the values from the relevant period but a year before (i.e. a year or a month).

I think maybe the clue is in attached document somewhere in the point "4.6.1 Numeric function".

Maciek