I have a Qlik Sense Table with a Dimension [Financial Year].
In the same table I have two calculated fields.
YTD = Sum all completed Months for the FY.
MAT = Sum the past 12 completed months.
Its the MAT field I am having issues with. as it reads months beyond the current FY.
E.g. Our FY is July 2018 to June 2019. Its March so we start at the last completed month which is Feb.
we need to calculate Feb 2019 back to March 2018 for the Column row FY 2019. this means I need to ignore the dimension for Financial Year to Calc the months March, April and June 2018 from the previous FY.
I have an Auto Generated calendar that I have marked the dates that are to be summed.
If(TempDate > date(floor(monthend(addmonths(Today(),-1)))),'MAT0',
If(TempDate > date(floor(monthend(addmonths(Today(),-13)))),'MAT1',
If(TempDate > date(floor(monthend(addmonths(Today(),-25)))),'MAT2','MAT3'))) as _fMATPeriods,
I have tried the following but it did not correctly give me the expected result.
Sum({$<[Financial Year] = >} If(vButtonCurrency=1 AND [_fMATPeriods]='MAT1',[Revenue AUD Amount] ...
Or this
Sum({<[Financial Year] = P({1})>} If(vButtonCurrency=1 AND [_fMATPeriods]='MAT1',[Revenue AUD Amount] ...
How do I ignore the Dimension field for the MAT Calc but not the YTD Calc?
Thanks in advance.
