YTD calculation reset at a New Year

[Anonymous]

Hello All,

The requirement is to do a YTD count of tickets for a duration of 12 months. The duration extends from Jun'16 to Jun'17. The x-axis has months and the y-axis has the count of tickets. The tricky part is, the tickets should be counted and accumulated and at the start of a new year(Jan 2017), the count should be reset to 0 and the accumulation process repeated.

Any suggestions on how to implement this requirement?

[sunny_talwar]

How are you doing the accumulation here? Using an expression or QlikView inbuilt Full Accumulation?

[Anonymous]

To be honest, I tried using expression with the full accumulation check, but it doesn't reset the count at the beginning of a new year. The reset is a critical part of this chart

[sunny_talwar]

What all are your dimensions and what is your expression?

[Anonymous]

My dimension is month, and expression being used is Count({<MonthofOccurence={">=$(=Num(YearStart(MonthofOccurenceMax)))<=$(=MonthofOccurenceMax)"},[Ticket Number] -={'0'}>}[Ticket Number]))

The expected outcome should be like this :

[sunny_talwar]

May be this

Aggr(RangeSum(Above(Count({<MonthofOccurence={">=$(=Num(YearStart(MonthofOccurenceMax)))<=$(=MonthofOccurenceMax)"},[Ticket Number] -={'0'}>}[Ticket Number]), 0, RowNo())), Year, Month)

[Anonymous]

Sadly, doesn't work. Shows a flat line.

[#8]

Please implement with AsOf Date Calendar with Rolling n-months. Please find the below document for reference.

Calculating rolling n-period totals, averages or other aggregations

Relative Calendar Fields

Accumulative Sums

[sunny_talwar]

Would you be able to share a sample?

[Anonymous]

Hi Sunny,

Will have to try regarding sharing the sample. However, could you help me with another query ?

I have this chart and I have used full accumulation on the expression. however, I want the full accumulation to stop at 2016 or somehow eliminate the accumulation for 2017 onwards using background colours or any other option. Could you help with the same ?