Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
Im creating a chart which displays the amount of new debtors per month. However, I want to only show the last 12 months in my chart and can't seem to fix it with a dimension limit.
This is the line of code I use to define the debtor start date and also use this as my month filter in the chart:
Load
*
, Month(Debtor_StartDate) & '/' & Year(Debtor_StartDate) as Debtor_StartMonth
Load
Date(Min(Date_Key)) as Debtor_StartDate
from
[.......]
Thanks in advance
I found a solution to my problem
This is what I added into the script and then used a Set Analysis to get in right into the graph.
Script:
If(Date <= Today() And Date >= SetDateYear(Today(), Year(Today())-1), 1, 0) As Last12Months
Set Analysis:
=Count({< Last12Months = {1}>} Debtor_StartDate)
Dimension limit really won't possible and it won't help for you
Create chart and use dim as month
and expression is
Sum({<Month = {">=AddMonths(Max(Month),-12) <=Max(Month)"}>}Measure)
I found a solution to my problem
This is what I added into the script and then used a Set Analysis to get in right into the graph.
Script:
If(Date <= Today() And Date >= SetDateYear(Today(), Year(Today())-1), 1, 0) As Last12Months
Set Analysis:
=Count({< Last12Months = {1}>} Debtor_StartDate)
Thank you so much for this Ivo. It looked so easy but have spent all morning working on it.
I understand this is very old question. I thought to share an alternative way to solve this.
I have created a variable to capture max-month in Load script (vMaxMonth) and then used this variable in the table/chart for the dimension as :
=if(Month>Addmonths(vMaxMonth,-12), Month)
Thought to share in case this may help others in the future.