Blank Weeks :=)

[Anonymous]

if(count(distinct(WEEK))='52',0,(52-count(distinct(WEEK))))

The above expression works perfectly to count blank weeks when there is actually 52 weeks of activity to measure.

It breaks down when you only say are 8 weeks into a new fiscal year and tells you a sales rep has 44 blank weeks, when in fact they only had 8 weeks to begin with as the other 44 havent happened yet 🙂

so question:

• How do I write an expression so that qliksense knows how many weeks are available in the selection? ie if we are 8 weeks in, how does Qlik know its 8 and see a sales rep only had activity in 5 and so has 3 blank weeks?

Thank for your help!

Ken H.

[luismadriz · Accepted solution]

Hi Ken,

Count(Total Distinct WEEK) should give you the total number of weeks after selections.

If you have a chart with Sales Reps, then Count(Distinct WEEK) should give you the number of weeks each has in the data, then another  measure could be Count(Total Distinct WEEK) - Count(Distinct WEEK) to give you the number of blanks weeks for each sales rep...

I may be completely off but I'd need more information or a data sample (with expected results) to help you better,

Cheers,

Luis

[shraddha_g]

How do I write an expression so that qliksense knows how many weeks are available in the selection? ie if we are 8 weeks in, how does Qlik know its 8 and see a sales rep only had activity in 5 and so has 3 blank weeks?

For this:

Do you have calendar in your data model?

If you have generated Master calendar by considering Min Date and Max Date from your Data then, in master calendar, all weeks will get generated and according to your selection of Sales Rep it will give you number of weeks for that SalesRep.

So expression given by luismadriz‌ should work.

Still It would be better if you provide sample data and expected output.

[Anonymous]

Thanks Luis, it was 98% correct, simply required additional parenthesis pon each end of the equation for some reason.

Really appreciate the help, have to learn these additional options of total etc.

Ken H.

[Anonymous]

(count(total distinct WEEK))-(count(distinct(WEEK)))

This ended up being the final expression.

So almost identical.

[luismadriz]

Hi Ken,

Do you need those parentheses around WEEK? count(distinct(WEEK))

Maybe the results are the same but it shouldn't work because distinct is not a function... well, I keep learning stuff everyday!

Cheers,

Luis

[Anonymous]

it seems I do, I tried and it gave wrong info

added those based on a hunch and it worked

essentially the same thing as you wrote, very minor changes

yes set analysis is an entirely new language! like learning Spanish or english