topic Year to Date calculation query in QlikView

Year to Date calculation query

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

Hello All,

I have a requirement to create a Year to date view of a ticket data coming in. It is a cumulative request and the ticket count should keep accumulating based on the tickets logged each month. If for a particular month there is no ticket details logged, the previous months count should be displayed as is. The counts should be reset at the beginning of every new year. I have created a chart based on the requirements, but I am facing an issue in the scenario wherein if for a particular month there are no tickets logged, no value is being displayed. Please refer to the chart below :

In the chart as you can see, there were no tickets logged for the dark blue section in the month of Dec'16, and the view showing up is incorrect. Similarly for Oct'17. The expression being used is as follows : Aggr(RangeSum(Above(Count(DISTINCT{<,[Nature Of Impact]={'Direct'},[Impact Type]={'High'},[Ticket Number]-={'0'}>}[Ticket Number]),0,RowNo())),Year,MonthofOccurence)

Could you please help out with this issue?

Re: Year to Date calculation query

Anil_Babu_Samineni — Thu, 07 Dec 2017 07:16:09 GMT

Not entire sure, Where we are. But try a luck the below one and suppress when value is not null should not be checked

Sum(Aggr(RangeSum(Above(Count(DISTINCT{<,[Nature Of Impact]={'Direct'},[Impact Type]={'High'},[Ticket Number]-={'0'}>}[Ticket Number]),0,RowNo())),Year,MonthofOccurence))

Re: Year to Date calculation query

Anonymous — Thu, 07 Dec 2017 07:18:42 GMT

try this...

RangeSum(Above(
Aggr(Count(DISTINCT{<,[Nature Of Impact]={'Direct'},[Impact Type]={'High'},[Ticket Number]-={'0'}>}[Ticket Number]),Year,MonthofOccurence)
,0,RowNo()))

Re: Year to Date calculation query

krishna20 — Thu, 07 Dec 2017 07:36:21 GMT

Hi Palani,

Please refer the attached doc for reference to generate missing records.

Re: Year to Date calculation query

Anonymous — Thu, 07 Dec 2017 09:39:40 GMT

Hi John, thanks for your response. I tried out your expression, but it is not providing the required output. It is accumulating it correctly, but doesn't reset the accumulation at the start of a new year. Which is a crucial aspect of the requirement.

Re: Year to Date calculation query

Anonymous — Thu, 07 Dec 2017 09:54:01 GMT

Hi Anil, Thank you for your response. I tried the expression you provided but unfortunately it isn't working too. There has been no change in the accumulation and the display remains as it is.

Re: Year to Date calculation query

Anil_Babu_Samineni — Thu, 07 Dec 2017 09:58:15 GMT

What are you trying to have this case instead of Single color?

Re: Year to Date calculation query

Anonymous — Thu, 07 Dec 2017 10:11:53 GMT

The colour is just a classification for different type of tickets, so that doesn't matter. The main issue is the data isn't getting accumulated if there is no entry record or a ticket generated for a particular month.

Re: Year to Date calculation query

Anil_Babu_Samineni — Thu, 07 Dec 2017 10:14:54 GMT

To understand better, Make it in straight table first then we can discuss further.

Re: Year to Date calculation query

Anonymous — Thu, 07 Dec 2017 10:59:48 GMT

Hi Anil, based on your suggestion..created a straight table.

Re: Year to Date calculation query

Anil_Babu_Samineni — Thu, 07 Dec 2017 11:37:38 GMT

Looks like you doesn't have Accumulated value. Can you attach the file to test

Re: Year to Date calculation query

sunny_talwar — Thu, 07 Dec 2017 12:36:29 GMT

How about this

Aggr(RangeSum(Above(Count(DISTINCT{<,[Nature Of Impact] = {'Direct'}, [Impact Type] = {'High'}, [Ticket Number] -= {'0'}>} [Ticket Number]) + Sum({1} 0), 0, RowNo())), Year, MonthofOccurence)

Re: Year to Date calculation query

Anonymous — Thu, 07 Dec 2017 12:36:34 GMT

For your reference.

Re: Year to Date calculation query

sunny_talwar — Thu, 07 Dec 2017 12:40:10 GMT

Does this look right?

Re: Year to Date calculation query

Anonymous — Thu, 07 Dec 2017 12:41:58 GMT

Hi Sunny, this expression worked perfectly. Thanks a lot. Could you please explain this expression, as I couldn't figure out what was wrong ?

Re: Year to Date calculation query

sunny_talwar — Thu, 07 Dec 2017 12:44:12 GMT

Just added a dummy value for all those MonthYears where they were null because of your set analysis.... the dummy value is Sum({1} 0) which allows you to accumulate as the value for the month year is now 0 instead of null.

Re: Year to Date calculation query

Anonymous — Thu, 07 Dec 2017 12:46:20 GMT

Great, thanks a lot.