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

[hemanthaanichet]

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

[neelamsaroha157]

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

[hemanthaanichet]

Hi Neelam,

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

Regards

Hemanth

[neelamsaroha157]

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.

[hemanthaanichet]

Hi Neelam,

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

i need show it for latest 12 months

[neelamsaroha157]

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?

[sunny_talwar]

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

[hemanthaanichet]

Hi All & Sunny,

Sry for the delay

attached a sample data pls do this needful asap.

Regards

Hemanth

[sunny_talwar · Accepted solution]

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?

[hemanthaanichet]

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