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

25 Replies
sunny_talwar

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

Capture.PNG

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)

Capture.PNG

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

hemanthaanichet
Creator III
Creator III
Author

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

sunny_talwar

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.

hemanthaanichet
Creator III
Creator III
Author

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



sunny_talwar

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

hemanthaanichet
Creator III
Creator III
Author

can you pls give the expression?  as iam fully in

sunny_talwar

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

hemanthaanichet
Creator III
Creator III
Author

Thanks Sunny

I will wait for your reply

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

hemanthaanichet
Creator III
Creator III
Author

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 






tanykozel
Contributor II
Contributor II

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