Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
In Variable Overview i have defined the following variable:
e_WP = sum({<LineType = {'Written'}, $(=Replace('$1', ';', ','))>}WP)
Now i want to use this variable in expressions like this
$(e_WP) for current selection which is working
if i use $(e_WP(MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month = )) i get the correct result for YTD
How should i define a new variable like vSetYTD and use as $(e_WP($(vSetYTD)))?
May be like this:
vSetYTD: MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month =
or
vSetYTD: =MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month =
and then this:
$(e_WP($(=vSetYTD)))
or
$(e_WP($(vSetYTD)))
May be like this:
vSetYTD: MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month =
or
vSetYTD: =MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month =
and then this:
$(e_WP($(=vSetYTD)))
or
$(e_WP($(vSetYTD)))
ty, it worked !
Now next part of my question.
I will have one year and one month selected. in one chart with year_month dimension i want to display for the last 12 month the YTD for the each of the Year_Month.
How should i define my set analysis variable?
Each of the Year_Month? Do you have a sample of what you are wanting to do?
I have the following table:
Year | Month | Year_Month | MonthID | WP |
2015 | 1 | 2015_1 | 1 | 10 |
2015 | 2 | 2015_2 | 2 | 20 |
2015 | 3 | 2015_3 | 3 | 5 |
2015 | 4 | 2015_4 | 4 | 15 |
2015 | 5 | 2015_5 | 5 | 23 |
2015 | 6 | 2015_6 | 6 | 21 |
2015 | 7 | 2015_7 | 7 | 12 |
2015 | 8 | 2015_8 | 8 | 45 |
2015 | 9 | 2015_9 | 9 | 46 |
2015 | 10 | 2015_10 | 10 | 32 |
2015 | 11 | 2015_11 | 11 | 21 |
2015 | 12 | 2015_12 | 12 | 5 |
2016 | 1 | 2016_1 | 13 | 10 |
2016 | 2 | 2016_2 | 14 | 15 |
2016 | 3 | 2016_3 | 15 | 20 |
2016 | 4 | 2016_4 | 16 | 21 |
2016 | 5 | 2016_5 | 17 | 24 |
2016 | 6 | 2016_6 | 18 | 45 |
2016 | 7 | 2016_7 | 19 | 56 |
2016 | 8 | 2016_8 | 20 | 34 |
2016 | 9 | 2016_9 | 21 | 23 |
2016 | 10 | 2016_10 | 22 | 12 |
2016 | 11 | 2016_11 | 23 | 18 |
2016 | 12 | 2016_12 | 24 | 6 |
So if the user select Year = 2016 and Month = 5 my output should be:
Year Month | YTD(WP) |
2015_6 | 94 |
2015_7 | 106 |
2015_8 | 151 |
2015_9 | 197 |
2015_10 | 229 |
2015_11 | 250 |
2015_12 | 255 |
2016_1 | 10 |
2016_2 | 25 |
2016_3 | 45 |
2016_4 | 66 |
2016_5 | 90 |
May be using an expression like this:
=RangeSum(Above(Sum({<Year, Month, Year_Month, MonthID>}WP), 0, Only({<Year, Month, Year_Month, MonthID>}Month)))
*
Avg({<Year_Month = {"$(='>' & Date(AddMonths(Max(Year_Month), -12), 'YYYY_M') & '<=' & Date(Max(Year_Month), 'YYYY_M'))"}, Year, Month, MonthID>}1)
I have added the breakdown of the two expressions for you to see why it is working the way it is working