Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
Hahahaha no, it is going to be much more complicated then this
can you pls give the expression? as iam fully in
Since it is slightly more complicated, I can only be able to help you once I am home.... Sorry buddy
Thanks Sunny
I will wait for your reply
Now much needed is that Query 2 and please help me in that query
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
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