topic Re: Year over Year & Month over Month Analysis in QlikView

Year over Year & Month over Month Analysis

Mon, 24 Nov 2014 17:00:28 GMT

Hi all,

I am trying to get a year over year analysis to work both by year and month selections.

When I select a year say 2014 and a specific month say 01, it works perfectly across both tabs (current and previous). The problem comes when I select multiple months, on the selected year tab, it's fine but on the previous year tab all the months are selected although the total is correct only for the selected months.

I have spent days to make it work but no luck. I hope someone could help if they had encounter this before.

Please feel free to ask for more clarification. Thank you. Jay

Re: Year over Year & Month over Month Analysis

salto — Mon, 24 Nov 2014 17:20:31 GMT

Hello Jeremaine,

I have two expressions. One for this year and other pfor the previous.

I am using Year (year of a date) and DateID in the calendar.

Sales 2014 (Max(Year):

=sum({$<Year={$(=Max(Year))}>} SalesAmount)))

Sales 2013 (which is the max(Year)-1);

=sum({<Year={$(=max(Year)-1)},DateID={"<=$(=date(addmonths(CurrentDay,-(NextYear-Max(Year))*12)))"}>} SalesAmount)))

Hope this helps!

Re: Year over Year & Month over Month Analysis

Tue, 25 Nov 2014 09:40:07 GMT

Hi Salto,

Thanks for this, I forgot to mention, I have 12 months as expressions.

I have a list of year from the date dimension, does this works when you select any year?

Regards,

Jeremaine

Re: Year over Year & Month over Month Analysis

salto — Tue, 25 Nov 2014 10:56:27 GMT

Hi Jeremaine,

yes it works, at least in my case. I select 1013 and shows sales for 2013 and 2012 (selected months)

Hth

Re: Year over Year & Month over Month Analysis

Tue, 25 Nov 2014 11:29:46 GMT

Thank you Salto,

It didn't work for me, can you please send me a demo file.

Thanks.

Re: Re: Year over Year & Month over Month Analysis

salto — Tue, 25 Nov 2014 12:24:21 GMT

Hello,

I have deleted some code, but please do check the (now corrected) expressions in the chart, and the relationship between them and the Year selected.

Hth.

Re: Re: Year over Year & Month over Month Analysis

Tue, 25 Nov 2014 13:31:06 GMT

There isn't any attachment SALTO, please check.

Re: Re: Re: Year over Year & Month over Month Analysis

salto — Tue, 25 Nov 2014 13:57:06 GMT

Weird, I can see it... anyway I am attaching it again.

Please let me know.

Re: Re: Re: Year over Year & Month over Month Analysis

Tue, 25 Nov 2014 14:47:22 GMT

Thank you, now I can see it. Will try it now and let you know how I get on.

Re: Re: Re: Year over Year & Month over Month Analysis

salto — Thu, 27 Nov 2014 10:29:39 GMT

Hello,

did you have any luck on this?

Re: Re: Re: Year over Year & Month over Month Analysis

Thu, 27 Nov 2014 11:05:16 GMT

Hi SALTO,

I managed to find a solution by just using this expression for all 12 months:

If(max(match(Month, '1')), Sum({<Year = {'$(vPreviousYear)'}, Month = {'1'}>} SaleValue),0)

The Year-over-Year already works it's just when I select multiple months all the months are selected although the total is correct for the selected month.

Thank you.

Jeremaine

Re: Re: Re: Year over Year & Month over Month Analysis

Thu, 27 Nov 2014 11:10:25 GMT

Screenshot of what it looks like now.

Thanks for all you help.