Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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

Highlighted
Creator III
Creator III

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

Highlighted

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.

Highlighted
Creator III
Creator III

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



Highlighted

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

Highlighted
Creator III
Creator III

can you pls give the expression?  as iam fully in

Highlighted

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

Highlighted
Creator III
Creator III

Thanks Sunny

I will wait for your reply

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

Highlighted
Creator III
Creator III

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 






Highlighted
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