topic Re: YTD Sum which is the sum of latest one Year. (Jun-2020-Jul-2019) in QlikView

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

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

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

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

rubenmarin — Sat, 18 Jul 2020 09:17:37 GMT

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

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

nevilledhamsiri — Sat, 18 Jul 2020 15:22:32 GMT

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

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

rubenmarin — Sun, 19 Jul 2020 08:46:25 GMT

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)

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

nevilledhamsiri — Sun, 19 Jul 2020 14:30:27 GMT

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

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

rubenmarin — Sun, 19 Jul 2020 18:43:40 GMT

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.

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

rubenmarin — Sun, 19 Jul 2020 18:54:56 GMT

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.

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

nevilledhamsiri — Mon, 20 Jul 2020 17:37:40 GMT

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.

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

rubenmarin — Tue, 21 Jul 2020 06:51:09 GMT

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.

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

nevilledhamsiri — Tue, 21 Jul 2020 18:03:45 GMT

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