count blank weeks

[Anonymous]

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))

[dionverbeke]

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

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

Thanks

I didn’t know how to create a master calendar

That’s great thanks