YTD Sum which is the sum of latest one Year. (Jun-2020-Jul-2019)

[nevilledhamsiri]

Dear All

I need to calculate the premium of each ME  for the latest 12 Month that is from Jun-2020 down to July 2019. Not the sum of premium for the entire period.

Thanks 

Neville

[rubenmarin]

Hi, but what you want to show? the table in green that is in excel? Sample attached

[nevilledhamsiri]

Yes. In short ,I need all MEs to have their figures for the period (July 2019 to Jun 2020). What I have coloured in green, is the period that should be considered for the premium calculation.If you can write an expression to capture the premium for the twelve Month period dynamically that is what I need. For eg, when we come to July 2020, the formula should be able to calculate the figures again for the latest 12 Months ( 2019 Aug to July 2020) etc.

Please  send me the expression on the body of the reply as I use personnel  edition.

Thanks

[rubenmarin · Accepted solution]

Hi, the expression is: 

Sum({<YearMonthDate={">=$(=AddMonths(Max(YearMonthDate), -11))"}>} PREMIUM)

And I also craeted a YearMonthDate field as MakeDate(Year(DATE), Month(DATE)).

You can change it to use the DATE field:

Sum({<DATE={">=$(=AddMonths(Max(DATE), -12))"}>} PREMIUM)

[nevilledhamsiri]

Hi Rubenmarin

Thanks a lot for the answer which is what I looked forward to. If possible please see how I could convert this calculation to arrive at a running range average. That is like for the latest twelve Months considered as we have got the premium for the said period, always at each Month it should reflects the average for eg ME Piyal should have his averages as below

2019        July    8450

2019       Aug      8150

2019      Sep       7850

2019     Oct         6738

Following formula seems to be not produce expected results. It is due to something missing somewhere or otherwise?

SUM(AGGR(RangeAvg(ABOVE( Sum({<DATE={">=$(=AddMonths(Max(DATE), -12))"}>} PREMIUM),0,RowNo())),BRANCH,TRAN_MONTH,ME,TRAN_YEAR))

Thanks for the solution provided in a very shorter expression!

Neville

[rubenmarin]

Hi Neville,

A running 12M can be more trivky, set analysis is calculated before the rows, so row values is not affected, all rows will have the same date filtered.

One way can be using an asof table: https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Another way is having it precalculated in script but this is only possible if dimensions and selections can work with fixed precalculated values.

[rubenmarin]

Also, if you use a YearMonth field instead of a field for year and another for month you can use an expression like:

RangeSum(Above(TOTAL Sum(PREMIUM), 0, RangeMin(Rowno(), 12)))

For this to work months should be consecutive so maybe you need to create data to fill missing year-months with zeros.

Ie, using Date(MakeDate(Year(DATE), Month(DATE)), 'MMM YYYY') AS YearMonthDate.

[nevilledhamsiri]

Hi Rubenmarin,

The expression with new field created is fantastic.As appeared below, for Ramesh, we considered always from the latest Month (Jun-2020) down to July-2019) as data are available for that period & more. (The figures in the 2nd column carry data for that range). As for the range sum total, it seems to be starting from May-2019 & go up to Jun-2020. Why this cannot be made for the same period (Jul-2019 To Jun-2020) which is the latest ytd 12 Months.(This is my requirement) Your former expression which is in the 2nd column, as I need, looks for the latest twelve Months (Jul-Jun).Can you please see why same range is not chosen for rangesum total as well.is it something wrong in the way I have applied your equation or otherwise?. 

Your explanations are very easy to follow & apply since matters are made less ambiguous.

Thank you for easy solutions even for matters which looks somewhat tricky.

[rubenmarin · Accepted solution]

Hi, the last expression was fr rolling 12M, so each row will sum itself and the 11 rows before it.

The first one was to fix the data for only the last 12 months.

A mix of both can be:

RangeSum(Above(TOTAL Sum({<DATE={">=$(=AddMonths(Max(DATE), -12))"}>} PREMIUM), 0, RangeMin(Rowno(), 11)))

If its not what you are looking for please post a table or an excel with the expected result.

[nevilledhamsiri]

Hi Dear

Issue was resolved. Thank you very much for taking the time in fixing the issue I had. Hope to receive the same kind of solutions that might come in my way.

Neville