Rolling 12 Months Sum with Sales greater than an amount

[pkumarm1]

Hello all,

I’ve been tasked to create a Rolling 12-month sum chart where the sales made by each active customer is greater than or equal to 5000.  I haven’t been able to find a solution from my research.  Hoping someone here can share their expertise.

I think one of the requirements is what makes it challenging is Rolling-12 Months Sum only for the customers who have made sales >=5000 in previous 12 monhts. 

AsofTable is out of option as the Data Model changes are restricted.

I'm using the below expression. It is working fine until I select a month. When I select a month for e.g. July 2023, it is reducing the amount:

RangeSum(Above(Sum({$<KUNDE.NR.NAME12={"=Sum({<KUNDE.ZUO.SME={'X'}, DATUM.MonJJJJ>}FAKT.VERK.UMSATZ)>=5000"}>}FAKT.VERK.UMSATZ), 0, 12)) * Avg(1)

Where

KUNDE.NR.NAME12--> Customer

KUNDE.ZUO.SME={'X'} --> Active Customer 

FAKT.VERK.UMSATZ--> Sales

DATUM.MonJJJJ --> MonthYear

Could someone please share their insights on this how to make this work?

Without MonthYear Selection:

With MonthYear Selection:

Many thanks!

Pawan M 

[robert99]

Hi @pkumarm1 

Just a comment on above.

I try to avoid rangesum(above...    due to the issue, you are finding. That is, it's not possible to drill down. When this happens the rolling total is reduced to the drilled down filter selections.

I was hoping chart level scripting might be a solution but it's not. Drill down still has the same impact

As-Of-Period is the only solution that I have found that work for a range on MonthYear as you show above

However, if you just want to just show the accumulated total for one filtered MonthYear, MonthsAgo might work  

Relative Calendar Fields - Qlik Community - 1473620

[robert99]

This might work if and only if you want to filter by one MthYear. By this I mean have no MthYear filters or just filter by 1 MthYear. Not 2 or more

if(GetSelectedCount(MthYear)  <> 1

,

rangesum(above (sum ({<GroupFSR = {FSR}

>}FSRMins/60  )

,0,RowNo()))

, //then

sum ({<GroupFSR = {FSR}

,MthYear =

, MonthsAgo = { ">=$(=max(MonthsAgo))  "}

,Year = {"=Year"}

>}TOTAL FSRMins/60  )

)

[robert99]

Another option using aggr

Calculating rolling n-period totals, averages or o... - Qlik Community - 148303

sum(aggr(rangesum(above(total sum({<Month=>}Amount),0,3)),Month))

[robert99]

sum(aggr(rangesum(above(total sum({<Month=>}Amount),0,3)),Month))

[pkumarm1]

Hi Robert, 

Thanks for your reply and suggestions. I tried the above mentioned appraoches but they don't seem to be working. 

I think one of the requirements is what makes it challenging is Rolling-12 Months Sum only for the customers who have made sales >=5000 in previous 12 months.  I'm not sure how to club this condition togehter.

Thanks!

[robert99]

Hi @pkumarm1 

Have you tried if and aggr .  I tried it once for debtors (remove negative balances) and it worked. I tried set analysis for days but there was always an issue (the example below was in the days before master measures could be used in a measure)

sum(    {<AsOfMthYr>}     aggr
(if(Sum({$<CanonType = {TransDate} ,GLTransType = {ARDetail,ARAllocate}>}ARValue)<0,0,

sum({$<CanonType = {TransDate} ,GLTransType = {ARDetail,ARAllocate}>} ARValue))

,AsOfMthYr ,CustName))

Ok just tested and needed to add a set analysis filter after the first sum (AsOfMthYr = should work too). But then it gave the correct totals by AsofMthYr 

AsOfMthYr is the dimension used. And Ignore CanonType and GLTransType. Select as appropriate for your business.

Qlik is weak in this area UNLESS AsOfPeriod is used. Then I did everything that was asked of me. To a high standard. Thats why I always set it up in script now almost without exception. As part of the calendar

See the limitation using the other option below

AGGR worked in a simple example I set up but only if I filtered by one Year. And had only one dimension

I couldn't get this to work for aggr with 2 dimensions. Rangesum (5th column) gave the correct totals but did not allow filtering by anything other than the full year

But this below did. But only if I filtered for one year (that gave the correct totals for the last 2 columns). Then I filtered by MthYear.  The total in the last column below agrees with the 2nd last column above. But the 2nd to last column is now wrong due to filtering

sum(aggr(
rangesum(above (TOTAL         sum ({<,MthYear =    >}Value )
,0,RowNo()))
,MthYear ))