topic Re: Month on Month Comparison. in QlikView

Month on Month Comparison.

Thu, 28 Jan 2016 08:22:17 GMT

Hi All,

I need to create a bar chart which shows the Month on Month added value.Ie.

The dimension would be MonthYear.

in Expression let say for 2015-12 it should show the sales which has been added compared to 2014-12,

sales(2015-12)-sales(2014-12) should be the added value for 2015-12.

How can i achieve the same.

Please suggest.

Regards,

Reena

Re: Month on Month Comparison.

Anonymous — Thu, 28 Jan 2016 08:28:16 GMT

Hi,

You can try the following:

Current Year =Sum({$<Year={$(=Max(Year))}, Mon={$(=Month(Today()))}>}Sales)

Previous Year =Sum({$<Year={$(=Max(Year)-1)}, Mon={$(=Month(Today()))}>}Sales)

Re: Month on Month Comparison.

Anonymous — Thu, 28 Jan 2016 08:29:05 GMT

You can replace the function Today() with a date field

Re: Month on Month Comparison.

MK_QSL — Thu, 28 Jan 2016 09:06:15 GMT

Provide sample data to work ! or may be your sample application

Re: Month on Month Comparison.

Thu, 28 Jan 2016 11:11:42 GMT

Hi Manish,

I have attached the sample data as well as Qvw.

Please find the attached.

Regards,

Reena

Re: Month on Month Comparison.

sunny_talwar — Thu, 28 Jan 2016 11:20:08 GMT

Try this expression:

sum({<Year={'$(=max(Year))'}>}Sales)

Above(sum({<Year={'$(=max(Year)-1)', '$(=max(Year))'}>}Sales))

Re: Month on Month Comparison.

Anonymous — Thu, 28 Jan 2016 11:28:55 GMT

sum({<Year={'$(=max(Year))'},{<Month={'$(=max(Month))'}>}>}Sales), sum({<Year={'$(=max(Year)-1)'},{<Month={'$(=max(Month))'}>}>}Sales)

Re: Month on Month Comparison.

Thu, 28 Jan 2016 11:29:01 GMT

Hi Sunny,

Thanks a lot for your reply.

With selections the expression is working. But if we are not selecting any Month then the chart is showin wrong results.

I have to show 2 years data without any selection and the chart should be independent of month selection. Based on selection of year it should show the same year data as well the previous year data.

Thank You

Re: Month on Month Comparison.

sunny_talwar — Thu, 28 Jan 2016 11:37:27 GMT

This may be:

sum({<Year={'$(=max(Year))'}>}Sales)

Above(sum({<Year={'$(=max(Year)-1)', '$(=max(Year))'}>}Sales), Count(DISTINCT TOTAL {<Year={'$(=max(Year))'}>} Month_Asof))

Re: Month on Month Comparison.

Thu, 28 Jan 2016 11:49:37 GMT

Hi Sunny,

Thanks again.

Now the values are appearing correct but it is only visible for year 2015..

Re: Month on Month Comparison.

sunny_talwar — Thu, 28 Jan 2016 11:54:00 GMT

I think there is no info about 2013. What would you want to see for 2014? the actual numbers?

Re: Month on Month Comparison.

sunny_talwar — Thu, 28 Jan 2016 11:56:25 GMT

For all years, may be this:

RangeSum(Sum(Sales), - Above(sum(Sales), Count(DISTINCT TOTAL {<Year={'$(=max(Year))'}>} Month_Asof)))

Re: Month on Month Comparison.

sunny_talwar — Thu, 28 Jan 2016 11:59:29 GMT

Or this for all years - first year in your data (because for first year you won't have any difference)

RangeSum(Sum({<Year -={'$(=min(Year))'}>}Sales), -Above(sum(Sales), Count(DISTINCT TOTAL {<Year={'$(=max(Year))'}>} Month_Asof)))

Re: Month on Month Comparison.

Thu, 28 Jan 2016 15:45:19 GMT

Error in expression

Re: Month on Month Comparison.

Fri, 29 Jan 2016 03:54:25 GMT

Hi Sunny,

It is working..

But if i have 1 more year data let say for 2013 then for 2013 it will show the same data.not the difference.

if i am limiting the data for 2 years in dimension.then for 2014 it will be showing the same value for 2014 not the difference.

And also if i need to add 1 more dimension "Type' then the expression wont Work..

Any suggestions ?

Regards,

Reena

Re: Month on Month Comparison.

jyothish8807 — Fri, 29 Jan 2016 07:31:38 GMT

Hi Reena,

Try using this :

RangeSum(Sum({<Year={$(=max(Year))}>}SALES), - Above(Sum({<Year={$(=max(Year)-1)}>}SALES), Count(DISTINCT TOTAL {<Year={'$(=max(Year))'}>} Month_AsOf)))
+ RangeSum(Sum({<Year={$(=max(Year)-1)}>}SALES), - Above(Sum({<Year={$(=max(Year)-2)}>}SALES), Count(DISTINCT TOTAL {<Year={'$(=max(Year))'}>} Month_AsOf)))

And for each type create different expressions eg Type={'ABC'}

Regards

Re: Month on Month Comparison.

sunny_talwar — Fri, 29 Jan 2016 08:27:55 GMT

Can you share data for 2013? I think this formula I just gave you should show you difference for 2014 and 2015 if you have 2013, 2014 and 2015 years in your database.

Re: Month on Month Comparison.

Fri, 29 Jan 2016 11:56:21 GMT

Thanks Jyothish. ... Its working..

Re: Month on Month Comparison.

VishalSingh18 — Mon, 02 Dec 2019 12:38:16 GMT

Can any1 help me with a expression that compares current and past month of same year

eg: nov 2019 comparison with oct 2019