Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Curiosity
Contributor
Contributor

Year To Date Calculation with missing Dates

Hello Qlikview Community.

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

[yearmonth] 

201807

201808

201811

[Sales]

1

3

4

[YTD Sales]

1

3

4

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

 

 

 

 

Labels (2)
4 Replies
Curiosity
Contributor
Contributor
Author

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

Greetings

Curiosity

PrashantSangle

Can you explain with required output.  

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Curiosity
Contributor
Contributor
Author

Hi PrashantSangle, 

thanks for your response!

Yes, the required Output would be:

[YTD Sales]

1

4

8

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

 

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.