Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
anything? anyone??
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.
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
You don't need the billetcount field, use Count(Distinct billet) for a billet count regardless of the employees.
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
so I did a count (distinct
{$
<bstatus={1}>
}
billet
)
it seems to work..