Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi stalwar1 Sunny,
I have a small issue, hope you would be able to help me out.
I have created a chart. Now I just want to show the max year in the form of bar and max(year) -1 in the form of line and get rid of any unnecessary data associated with previous years.
My max year is 2016
And prior year is 2015.
So on x-axis I'll have months from Jan -Dec. Basically bars from Jan - Sep(since I don't have data for 2016 Oct, 2016 Nov and 2016 Dec). And a line for Jan - Dec
Thanks for all the help.
This should do exactly that. is it not?
Sum({<YR_MTH_KEY = {"=Month(YR_MTH_KEY) = Month(Max(TOTAL YR_MTH_KEY))"}>} CRRNT_PRCPL_BAL_AMT)
I think the best option would be to create a Year field in the script if you are able to modify your script. Can you?
Without changes may be like this
Dimension
=Month(YR_MTH_KEY)
Expressions
Sum({<DLQ_STATUS = {1}, YR_MTH_KEY = {"=Year(YR_MTH_KEY) = Max(TOTAL Year(YR_MTH_KEY))"}>}CRRNT_PRCPL_BAL_AMT)/sum({<DLQ_STATUS = {1}>}CRRNT_PRCPL_BAL_AMT2)
Sum({<DLQ_STATUS = {1}, YR_MTH_KEY = {"=Year(YR_MTH_KEY) = (Max(TOTAL Year(YR_MTH_KEY))-1)"}>}CRRNT_PRCPL_BAL_AMT)/sum({<DLQ_STATUS = {1}>}CRRNT_PRCPL_BAL_AMT2)
Yes I can do that Sunny. I want to create two variables in front end.
vCurrentYear = year(max(YR_MTH_KEY))
vPriorYear = year(max(YR_MTH_KEY)) - 1
And want to have two expressions as sum({<DLQ_STATUS = {1},Year ={$(vCurrentYear)}>}CRRNT_PRCPL_BAL_AMT)/sum({<DLQ_STATUS = {1},Year ={$(vCurrentYear)}>}CRRNT_PRCPL_BAL_AMT2)
sum({<DLQ_STATUS = {1},Year ={$(vPriorYear)}>}CRRNT_PRCPL_BAL_AMT)/sum({<DLQ_STATUS = {1},Year ={$(vPriorYear)}>}CRRNT_PRCPL_BAL_AMT2) . And the dimension as Month.
But the challenge is, I'm not able to convert the date field into month and year..
Why not? What is the issue you are running into while converting date field to Month and Year?
Sunny sorry to bug you again but one last thing..
I want to show current year max month (2016 - Aug) and prior year max month of current year (2015 - Aug).
So I gave my expression as for Current year max month
Sum({<YR_MTH_KEY = {"=Month(YR_MTH_KEY) = Max(TOTAL Month(YR_MTH_KEY))"}>}CRRNT_PRCPL_BAL_AMT)
But not sure how to give the expression for prior year max month of current year.
Thanks for all the help. Just can't say it enough..
May be try this (reorder Max and Month in the set modifier:
Sum({<YR_MTH_KEY = {"=Month(YR_MTH_KEY) = Month(Max(TOTAL YR_MTH_KEY))"}>} CRRNT_PRCPL_BAL_AMT)
Sunny, this gives me max month but not previous year current month right.
Previous year current month? Not sure I follow?
Say the max Month in current year is Oct, i.e 2016-Oct
Max Month in previous year would be Dec, i.e 2015-Dec
But I want 2015 Oct from prev year.
Because when comparing YOY in the charts for max month, I shouldn't compare 2016 Oct with 2015 Dec right?
I want to compare 2016 Oct with 2015 Oct.
Hope it makes sense.