Counting only dimensions containing data

jlampard40 — Tue, 24 Aug 2021 11:41:30 GMT

Hi - I have a set of data which includes date of stat (UNV_StatDate) and I'm using a count of unique event key (UNV_EventKey).

My brief is to try and show which staff have vetted the most documents between the first stat date and last stat date. The problem is, this keeps changing on a daily basis as it is updating all the time.

I initially tried something really simple and built a straight table (see attachment). I build an expression for 02/08/2021 (the first stat date in the data) and 24/08/2021 (the last available stat date). I then worked out the difference between the two and calculated a % based on this. My problem is that some of the UNV_IntRad names will not have any data in the 24/08/2021 and so will show a zero, thus making my calculations incorrect. For example, for some of the UNV_IntRad fields their last stat date with data could be 07/08/2021, or 10/08/2021 etc etc. So, I just need to have a way (using set analysis if possible) to tell Qlikview to just count from the first to last (most recent) stat date which has data in it, for each UNV_IntRad?

Does this makes sense? Many thanks in advance guys. Rgds

Re: Counting only dimensions containing data

edwin — Tue, 24 Aug 2021 23:29:03 GMT

if i understand the ask, you want to apply our expression only when UNV_IntRad has data in some field associated with it and not that UNV_IntRad is not null. does this mean there is another field that you wanted to check if it has data or not? regardless, and assuming by no data, the field in question is null. you can check for it by using the modifier FIELD={"*"}

sum({<FIELD={"*"}>}MEASURE)

this makes sure that the field is checked first for not null. hope that helps

topic Counting only dimensions containing data in QlikView

Counting only dimensions containing data

Re: Counting only dimensions containing data