6 Replies Latest reply: Nov 29, 2017 11:57 PM by Kenneth Holden RSS

    Blank Weeks :=)

    Kenneth Holden

      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.

        • Re: Blank Weeks :=)
          Luis Madriz

          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

          • Re: Blank Weeks :=)
            Shraddha Gajare
            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.