Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
kennethholden
Contributor

count blank weeks

I am looking how rtf create an expression to count blank weeks for my sales rep.

Currently it counts the weeks they wrote business and it divides by the same,, The issue is if they write business in 40 weeks then they have 12 bank weeks and the average should be smaller.

how do I tell qilksense to count blank weeks?

thanks.

Count(distinct(PACKAGE_ID))/count(distinct(WEEK))

Tags (1)
1 Solution

Accepted Solutions
luismadriz
Valued Contributor

Re: count blank weeks

Hi Ken,

I'd agree with Dion that you may want to use a Master Calendar. If you choose to use 52, then you may be limiting the analysis to yearly for when users makes different selections... but any way if it works for you it works!

Just in case it helps, I created a process to automatically create a master calendar with few parameters: https://community.qlik.com/docs/DOC-19202

Cheers

L

4 Replies
Luminary
Luminary

Re: count blank weeks

Probably the issue comes because you did not generate the missing weeks.

1.Ideally you use a master calendar or qlik components to fill in the gaps.

They use autogenerate to fill in the gaps.

2.If the blanks are in your dataset you can use Isnull or trim to identify them and create a field WEEK_CNT to count them.

Kind Regards,

Dion

kennethholden
Contributor

Re: count blank weeks

Thanks for the answer.


I used an if statement with =52 then 0 and if not then count of distinct weeks -52 and it seemed to work well


I will look into your other suggestions 


Thanks 

luismadriz
Valued Contributor

Re: count blank weeks

Hi Ken,

I'd agree with Dion that you may want to use a Master Calendar. If you choose to use 52, then you may be limiting the analysis to yearly for when users makes different selections... but any way if it works for you it works!

Just in case it helps, I created a process to automatically create a master calendar with few parameters: https://community.qlik.com/docs/DOC-19202

Cheers

L

kennethholden
Contributor

Re: count blank weeks

Thanks


I didn’t know how to create a master calendar


That’s great thanks