topic Re: Advanced counting/adding involving several fields in QlikView

Advanced counting/adding involving several fields

Anonymous — Wed, 17 Oct 2018 18:37:29 GMT

Please consider the following excerpt from a fact table:

Model SKU Jobs

=============================

Model3 SKU5 0

Model1 SKU1 1

Model1 SKU2 1

Model1 SKU2 0

Model1 SKU3 0

Model2 SKU4 1

Model3 SKU5 1

Model1 SKU2 0

Model3 SKU6 1

Model1 SKU2 0

Model4 SKU7 1

Model1 SKU2 0

... ... ...

This is the logic:

Each model is associated with a set of SKUs, exclusive of each model.
An SKU will never undergo more that 1 job.
A model is processed when each and every of its SKUs has undergone one job.
A model is partially processed when one or more, but not all, of its SKUs are have undergone a job.
A model is unprocessed when none of its SKUs has undergone a job.

Now I would like to define these measures enabling visualizations based on the selected data set:

Your help will be greatly appreciated.

lironbaram — Thu, 18 Oct 2018 18:36:50 GMT

you can use these expressions :

sum(aggr(if(count(distinct SKU)=sum(Jobs),1,0),Model))

sum(aggr(if(count(distinct SKU)>sum(Jobs) and sum(Jobs)>0,1,0),Model))

sum(aggr(if(sum(Jobs)=0,1,0),Model))

Anonymous — Fri, 26 Oct 2018 07:38:52 GMT

Thanks Liron for your time and expertise.
You have really helped my learning process!

lironbaram — Fri, 26 Oct 2018 17:23:44 GMT

Hi Sergio

It's my pleasure