Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
tuanmaciek
Creator
Creator

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.

1 Solution

Accepted Solutions
tuanmaciek
Creator
Creator
Author

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

YearMonth_170113.png

View solution in original post

18 Replies
galax_allu
Specialist
Specialist

Hi

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

Current month (last year)   

      

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

Current month (this year)  

                

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

so for your situation :

try this ..

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

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

tuanmaciek
Creator
Creator
Author

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) /

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

galax_allu
Specialist
Specialist

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)

tuanmaciek
Creator
Creator
Author

1) Yes

2) It gives 0 as a result.

tuanmaciek
Creator
Creator
Author

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.

Sum({$<Year={"$(=year(addyears(max(DocumentDate),-1)))"},Category={"Sales"}>}Amount)


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)/

Sum({$<Year={"$(=year(addyears(max(DocumentDate),-1)))"},Category={"Sales"}>}Amount)

Data_170104.png

vinieme12
Champion II
Champion II

Create a MonthYear field like below

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

Expression for Previous Year Same Month

Sum({$<[MonthYear]={"$(= Date(AddYears( Date(max(DocumentDate)),-1),'YYYYMM') )"},Category={"Sales"}>}Amount)

tuanmaciek
Creator
Creator
Author

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)/

Sum({$<Year={"$(=year(addyears(max(DocumentDate),-1)))"},Month-={">$(=$(varMonthMax))"},Category={"Sales"}

>}Amount)

where

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

tuanmaciek
Creator
Creator
Author

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.

parimikittu
Creator II
Creator II

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