Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following expression and I want to only see the Max New_Reporting_Period but for some reason I can't get my head round it
=count(distinct If(DaysToTS_Acc<21,Risk_No))
I'm sure it's simple!
Actually these might also work:
Max
Count(DISTINCT {<DaysToTS_Acc = {'<21'}, New_Reporting_Period = {"$(=Max(New_Reporting_Period))"}>} Risk_No)
2nd Max
Count(DISTINCT {<DaysToTS_Acc = {'<21'}, New_Reporting_Period = {"$(=Max(New_Reporting_Period, 2))"}>} Risk_No)
May be this
FirstSortedValue(Aggr(Count(DISTINCT {<DaysToTS_Acc = {'<21'}>} Risk_No), New_Reporting_Period), -New_Reporting_Period)
This should show the count for the Max New_Reporting_Period
What is the format of New_Reporting_Period field?
Awesome, thanks Sunny, and to do the previous month?
This
FirstSortedValue(Aggr(Count(DISTINCT {<DaysToTS_Acc = {'<21'}>} Risk_No), New_Reporting_Period), -New_Reporting_Period, 2)
Actually these might also work:
Max
Count(DISTINCT {<DaysToTS_Acc = {'<21'}, New_Reporting_Period = {"$(=Max(New_Reporting_Period))"}>} Risk_No)
2nd Max
Count(DISTINCT {<DaysToTS_Acc = {'<21'}, New_Reporting_Period = {"$(=Max(New_Reporting_Period, 2))"}>} Risk_No)
Yes, these worked perfectly. Thank you
Last question on this, can this be adapted to be a rolling 3 month? so included the Max 3 months?
May be this
Count(DISTINCT {<DaysToTS_Acc = {'<21'}, New_Reporting_Period = {"$(='>=' & Max(New_Reporting_Period, 3) & '<=' & Max(New_Reporting_Period))"}>} Risk_No)