excel to qlik thinking

[Anonymous]

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

Thanks,

Maria

[Anonymous]

anything?  anyone??

[petter]

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.

[Anonymous]

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

[jonathandienst]

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

[Anonymous]

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

[Anonymous · Accepted solution]

so I did a count (distinct

{$

<bstatus={1}>

}

billet

)

it seems to work..