6 Replies Latest reply: Mar 3, 2017 10:00 AM by Maria Harmening RSS

    excel to qlik thinking

    Maria Harmening

      so, in excel I know how to do this action.  however, I need some assistance doing it in qlik.


      I have billets that are active during a certain timeframe.  I have them marked with 1s and 0s based on the dates.  however, sometimes they're stuffed with two  people...so, my people status would reflect two, but I only want to show the one billet as active. in excel I would do a 1/sumproduct to calculate a billet count.  how would I do this during a qlik load?


      for instance


      billet           bstatus           emp            empstatus        billetcount

      aaa             1                     Jane           1                      .5

      aaa             1                     John           1                      .5

      bbb              1                     James        1                       1

      bbb              0                      Julie           0                      0

      ccc               1                      Calvin        1                      .3

      ccc               1                      Charlotte    1                      .3

      ccc                1                     Conner        1                     .3




        • Re: excel to qlik thinking
          Maria Harmening

          anything?  anyone??

            • Re: excel to qlik thinking
              Petter Skjolden

              Could you explain more in detail?


              You refer to "based on dates" but there is no date in your sample data. You refer to a SumProduct but you dont say over which range you do the SumProduct ... You refer to people status but all I see is emp status.

                • Re: excel to qlik thinking
                  Maria Harmening

                  the empstatus is the people status (sorry for the terminology change).  the way that I calculated the billetcount in excel was using a sumproduct...1/SUMPRODUCT(($billet$1:$billet$7=billet1)*($month$1:$month$7=month1))*bstatus

                  obviously this is a very simplistic example, and it the data would account for all twelve months of the year.


                  billet           bstatus           emp            empstatus        billetcount        MonthDateDimension

                  aaa             1                    Jane             1                      .5                     Feb 2017    there are two people 

                  aaa             1                    John             1                      .5                     Feb 2017    in this one billet for Feb 2017

                  bbb             1                    James          1                       1                     Feb 2017     one person in one billet for Feb 2017

                  Null             0                    Julie             1                       0                     Feb 2017     Julie is on board but not in a billet

                  ccc             1                    Calvin           1                      .3                     Feb 2017      there are 3 people

                  ccc             1                    Charlotte      1                      .3                     Feb 2017      in this billet

                  ccc             1                    Conner         1                      .3                      Feb 2017      in Feb 2017

                    • Re: excel to qlik thinking
                      Jonathan Dienst

                      You don't need the billetcount field, use Count(Distinct billet) for a billet count regardless of the employees.

                        • Re: excel to qlik thinking
                          Maria Harmening

                          in the measure for the chart, how would I incorporate the billetstatus into the count(distinct)?  would it be count(distinct billet) if bstatus = 1???    the syntax is still throwing me off.  sorry.  here would be a scenario for that..


                          billet           bstatus           emp            empstatus        billetcount        MonthDateDimension

                          ddd             0                    Null              0                      0                     Feb 2017       the billet is inactive

                          eee             1                    Null              0                      1                     Feb 2017       no one is in the billet but it's active

                          Null             0                    Jon              1                      0                      Feb 2017      not assigned to a billet