Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pranaligawde
Contributor III
Contributor III

Try to achive running count of ids for current month to last year same month

Hello All,

Try to achive running count of ids for current month to last year same month.but without restriction.Asi want to showfor current year month till previous year same month.But it should conider all the values before current month .bit it is considering from selected month only.

Dimension :MonthYear

Measure:

RangeSum(Above( total count({<period={">=$(prevyeardate)<=$(curryeardate)"}>} distinct Management_Escalations_Open_Cases_for_balance_CI)

,0,RowNo()))

 

12 Replies
pranaligawde
Contributor III
Contributor III
Author

Hello @tresesco 

Without any selection i need to show on bar chart monthyear from jun19 till may20 and it should get accumulated value  from 11 months back which is jul 19.

tresesco
MVP
MVP

Try exp:

rangesum( above( sum( {<Month_Year1={">=$(=Date(MonthName(Max(Month_Year1),-11),'MMM-YY')) <$(=Max(Month_Year1))"}>}  Quantity),0,11) )

 

without accumulation in chart setting.

pranaligawde
Contributor III
Contributor III
Author

Hello @tresesco ,

i achived output with this set anaysis .

 

vPeriodType=   '<=$(=Date(MAX(Month_Year1),'MMM-YY')) >=$(=Date(AddMonths(MAX(Month_Year1),-11),'MMM-YY'))'

=if(
Count({<Month_Year1={"$(vPeriodType)"},Month=,Year=>}[Complaint ID in Source]) <> 0 ,
RangeSum(Above(Count(distinct [Complaint ID in Source]), 0, 12)))

But now my user ask is if 12 months are not present while calculatin 12 months rolling then in that monthyear value suppose to 0.

for eg: In chart  it is showing 12 month from jun19 to May 20 in that case if there is no sufficient month before jun19 (i.e 12 month befroe jun) then jun19 value should be zero.And chart should calculate other month value as per 12 month logic.

 plz advise how i can alter my formula to achive this.

appreciate your help in advance.