cancel
Showing results for
Did you mean:
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:

-

/

i wanna to show to the chart like below but in bar chart

Regards

Hemanth

25 Replies
MVP

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

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

MVP

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.

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

MVP

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

Creator III
Author

can you pls give the expression?  as iam fully in

MVP

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

Creator III
Author

Thanks Sunny

Creator III
Author

Hi Sunny,

Finally I solved my Query 2 .

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

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

Community Browser