Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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 )
)
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))
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!
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 ))