Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hemanthaanichet
Creator III
Creator III

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

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

Latest 12months.PNG

Regards

Hemanth

1 Solution

Accepted Solutions
sunny_talwar

Do you want to get this?

Capture.PNG

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?

View solution in original post

25 Replies
neelamsaroha157
Specialist II
Specialist II

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

hemanthaanichet
Creator III
Creator III
Author

Hi Neelam,

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

Regards

Hemanth

neelamsaroha157
Specialist II
Specialist II

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

Is this what you are looking for

Capture.PNG

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
Creator III
Creator III
Author

Hi Neelam,

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

i need show it for latest 12 months

neelamsaroha157
Specialist II
Specialist II

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
Creator III
Creator III
Author

Hi All & Sunny,

Sry for the delay

attached a sample data pls do this needful asap.

Regards

Hemanth

sunny_talwar

Do you want to get this?

Capture.PNG

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
Creator III
Creator III
Author

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