Advanced counting/adding involving several fields

[Anonymous]

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:

• Count of processed models
• Count of partially processed models
• Count of unprocessed Models

Your help will be greatly appreciated.

[lironbaram · Accepted solution]

hi

you can use these expressions :

• Count of processed models

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

• Count of partially processed models

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

• Count of unprocessed Models

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

[Anonymous]

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

[lironbaram]

Hi Sergio

It's my pleasure