topic Year To Date Calculation with missing Dates in QlikView

Year To Date Calculation with missing Dates

Curiosity — Sat, 16 Nov 2024 00:50:58 GMT

Hello Qlikview Community.

I have a pivot table with the following three columns (+ 3 example values):

[yearmonth]

201807

201808

201811

[Sales]

[YTD Sales]

The [YTD Sales] values are obviously wrong.

For [Sales] I used the expression: =COUNT(DISTINCT [Sales])

For [YTD Sales] I used the expression: = RANGESUM(Above(Count(DISTINCT [Sales]),0,Num(RIGHT([yearmonth],2))))

The YTD Expression is working on another Pivot Table where I have an entry for every [yearmonth]. Is there a way to show every [yearmonth]? Or do you have an alternative solution?

Thanks very much for the support.

Greetings

Curiosity

Re: Year To Date Calculation with missing Dates

Curiosity — Wed, 15 Apr 2020 07:24:42 GMT

Nobody an idea? Is the question not clear enough? Would be happy for any response

Greetings

Curiosity

Re: Year To Date Calculation with missing Dates

PrashantSangle — Wed, 15 Apr 2020 07:28:43 GMT

Can you explain with required output.

Re: Year To Date Calculation with missing Dates

Curiosity — Wed, 15 Apr 2020 11:26:25 GMT

Hi PrashantSangle,

thanks for your response!

Yes, the required Output would be:

[YTD Sales]

I have multiple Years so if its the same year (here 2018) you accumulate the numbers and next year you should start with the first entry from next year.

Greetings

Curiosity

Re: Year To Date Calculation with missing Dates

Brett_Bleess — Wed, 13 May 2020 20:10:09 GMT

Have a look at the following Design Blog posts:

https://community.qlik.com/t5/Qlik-Design-Blog/Year-over-Year-Comparisons/ba-p/1462927

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

I believe those should be of some help.

Regards,
Brett