topic Re: To show growth of current year MAT vs previous year MAT in bar chart in QlikView

To show growth of current year MAT vs previous year MAT in bar chart

hemanthaanichet — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a max data of Jun 2016 in my Table and have to take the growth comparing current year 12 months vs previous year 12 months from the max data having for the current year.

Am using Yearmonth as a dimension in Bar chart

and using below given expression:

(Sum({<Yearmonth ={">=$(=date(addmonths(date#(max(Yearmonth ),'MMM-YY'),-11),'MMM-YY')) <=$(=max(Yearmonth ))"}>}NVALUE)

Sum({<Yearmonth ={">=$(=Date(Addmonths(Addyears(Date#(Max(Yearmonth ),'MMM-YY'),-1),-11),'MMM-YY')) <=$(=Date(Addyears(Date#(Max(Yearmonth ),'MMM-YY'),-1),'MMM-YY'))"} >} NVALUE))

(Sum({<Yearmonth ={">=$(=Date(Addmonths(Addyears(Date#(Max(Yearmonth ),'MMM-YY'),-1),-11),'MMM-YY')) <=$(=Date(Addyears(Date#(Max(Yearmonth ),'MMM-YY'),-1),'MMM-YY'))"} >} NVALUE))

i wanna to show to the chart like below but in bar chart

Regards

Hemanth

Re: To show growth of current year MAT vs previous year MAT in bar chart

neelamsaroha157 — Thu, 12 Jan 2017 13:53:55 GMT

What is the issue you are facing when converting this line chart to bar chart??

Re: To show growth of current year MAT vs previous year MAT in bar chart

hemanthaanichet — Thu, 12 Jan 2017 15:49:57 GMT

Hi Neelam,

actually i doesn't find a better screenshot than this to show whats my exact requirement is.

Regards

Hemanth

Re: To show growth of current year MAT vs previous year MAT in bar chart

neelamsaroha157 — Thu, 12 Jan 2017 16:50:23 GMT

Ohh I am sorry, i thought you have already created this chart

Is this what you are looking for

If yes then expression that is used here are

Current: Sum({$<Date={"<$(=Max(Date))>$(=AddMonths(Max(Date), -11))"}, Year=, Month =>}Value)

Previous: Count({$<Date={"<$(=AddMonths(Max(Date), -12))>$(=AddMonths(Max(Date), -23))"}, Year=, Month =>}RESPONSE)

In my scenario max date is in Oct.

Re: To show growth of current year MAT vs previous year MAT in bar chart

hemanthaanichet — Thu, 12 Jan 2017 17:00:50 GMT

Hi Neelam,

Not exactly but i think u r in the way of my requirement

i need show it for latest 12 months

Re: To show growth of current year MAT vs previous year MAT in bar chart

neelamsaroha157 — Thu, 12 Jan 2017 17:07:53 GMT

Thats what it is doing here

My max Mo nth is Oct-2016 and its going back from Oct-2016 to Nov-2015 as Current

And Nov-2015 to Oct-2014.

Are you looking for some other time frames?

Re: To show growth of current year MAT vs previous year MAT in bar chart

sunny_talwar — Thu, 12 Jan 2017 17:20:12 GMT

Might be helpful if you can share a sample with us (may be raw data) with the expected output from it

Re: To show growth of current year MAT vs previous year MAT in bar chart

hemanthaanichet — Mon, 16 Jan 2017 05:50:50 GMT

Hi All & Sunny,

Sry for the delay

attached a sample data pls do this needful asap.

Regards

Hemanth

Re: To show growth of current year MAT vs previous year MAT in bar chart

sunny_talwar — Tue, 17 Jan 2017 02:08:47 GMT

Do you want to get this?

Expression:

(Sum({<YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -11), 'MMM-YY') & '<=' & Date(Max(YearMonth), 'MMM-YY'))"}>}Sales)

Above(Sum({<YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -23), 'MMM-YY') & '<=' & Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))"}>}Sales), 11)) - 1

Not sure I understand your second Query. What is that you need there? May be share your expectation in terms of numbers you would want to see?

Re: To show growth of current year MAT vs previous year MAT in bar chart

hemanthaanichet — Tue, 17 Jan 2017 05:04:20 GMT

Hi Sunny ,

Exactly the above i need to show .

But only one thing i would like to know why the Sep-13 is not displaying because including that i has to show.

As Sep13 is the latest of month.

When i manually check the answer for Aug13 with my regular formula i.e, CY-PY/PY . it is correct

One more if you dont mistaken can you please explain the denominator expression a bit clear ,how it works?

Second Query :

Rollling Quater Growth:

Formula: ( RQTR of Current Year - RQTR of Previous Year) / (RQTR of Previous Year )

Current year is latest 12 months i.e., Oct-12 - Sep-13

Previous year is previous latest 12 months i.e., Oct-11 - Sep12

Dimension should be latest 12 months i.e., Oct-12 - Sep-13

Sep-13 have a value which is equal to sum of sales for Sep-13,Aug-13,Jul-13

i.e., Sep-13 = sum({<Yearmonth={ Sep-13,Aug-13,Jul-13}>}sales)

Aug-13 = sum({<Yearmonth={ Aug-13,Jul-13,Jun-13}>}sales)

Jul-13 = sum({<Yearmonth={ Jul-13,Jun-13,May-13}>}sales)

like so on for remaining

Regards

Hemanth

Re: To show growth of current year MAT vs previous year MAT in bar chart

sunny_talwar — Tue, 17 Jan 2017 10:52:25 GMT

So for your 1st query, if I use this expression

Sum({<YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -23), 'MMM-YY') & '<=' & Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))"}>}Sales)

I will see this in a straight table

But at this time, you cannot perform your calculation because PY number is not in front of CY number. So to push it down you can use Above() function. and I have to push it down 11 steps. So I used this

Above(Sum({<YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -23), 'MMM-YY') & '<=' & Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))"}>}Sales), 11)

Now I think the issue with your YearMonth field is that it is not always the monthstart date. I would fix that in the script like this

Date(MonthStart(YearMonth), 'MMM-YY') as YearMonth

But right now, this seems to work

(Sum({<YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -11), 'MMM-YY') & '<=' & Date(AddMonths(Max(YearMonth), 1), 'MMM-YY'))"}>}Sales)

Above(Sum({<YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -23), 'MMM-YY') & '<=' & Date(AddMonths(Max(YearMonth), -11) + 1, 'MMM-YY'))"}>}Sales), 12)) - 1

Give me some time and I will work on your 2nd query at a time when I have some more time to look into it.

Best,

Sunny

Re: To show growth of current year MAT vs previous year MAT in bar chart

hemanthaanichet — Tue, 17 Jan 2017 12:33:13 GMT

Hi Sunny,

In my Query1 is there any way to use the second dimension .i.e suppose if i have any products under the yearmonth

Regards

Hemanth

Re: To show growth of current year MAT vs previous year MAT in bar chart

sunny_talwar — Tue, 17 Jan 2017 12:42:37 GMT

With the same date range? I think you should be able to add it right away. But if each product will have a different max date, then you won't be able to use set analysis and will have to use Aggr() function to make that to work.

Re: To show growth of current year MAT vs previous year MAT in bar chart

hemanthaanichet — Tue, 17 Jan 2017 13:25:22 GMT

Hi Sunny,

Below expression is that you are saying

((Aggr(Sum({<YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -11), 'MMM-YY') & '<=' & Date(AddMonths(Max(YearMonth), 1), 'MMM-YY'))"}>}Sales),Product))

(Aggr(Above(Sum({<YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -23), 'MMM-YY') & '<=' & Date(AddMonths(Max(YearMonth), -11) + 1, 'MMM-YY'))"}>}Sales), 12),Product)) )- 1

Re: To show growth of current year MAT vs previous year MAT in bar chart

sunny_talwar — Tue, 17 Jan 2017 13:31:29 GMT

Hahahaha no, it is going to be much more complicated then this

Re: To show growth of current year MAT vs previous year MAT in bar chart

hemanthaanichet — Tue, 17 Jan 2017 13:39:17 GMT

can you pls give the expression? as iam fully in

Re: To show growth of current year MAT vs previous year MAT in bar chart

sunny_talwar — Tue, 17 Jan 2017 13:43:31 GMT

Since it is slightly more complicated, I can only be able to help you once I am home.... Sorry buddy

Re: To show growth of current year MAT vs previous year MAT in bar chart

hemanthaanichet — Tue, 17 Jan 2017 15:21:31 GMT

Thanks Sunny

I will wait for your reply

Now much needed is that Query 2 and please help me in that query

Re: To show growth of current year MAT vs previous year MAT in bar chart

hemanthaanichet — Thu, 19 Jan 2017 06:10:08 GMT

Hi Sunny,

Finally I solved my Query 2 .

Thank for your helpful Ideas,Suggestions and Trick work

Here is the expression:

((above(rangesum(below(sum({<Year=,Month=,YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -13), 'MMM-YY') & '<=' & Date(Max(YearMonth), 'MMM-YY'))"}>}Sales),0,3)),2))

(above(rangesum(below(sum({<Year=,Month=,YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -25), 'MMM-YY') & '<=' & Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))"} >}Sales),0,3)),14)))-1

Please let me know if you have any idea about adding the secondary dimension

Re: To show growth of current year MAT vs previous year MAT in bar chart

tanykozel — Thu, 14 Sep 2017 07:02:15 GMT

Sunny, hello

Thanks for your explanation. Could you pls help me.

I need to show on one combo chart sales (in bar ) and growth of sales products (in line) on time line (month+year). As in additional to your attached file