Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an expression which calculates the percentage of class 2 and waste generated in an individual production run and presents this in a chart.
Run | Product | Category | Variety | Class 1 count | Class 2 count | Waste count | total count | Class 2 % | Waste % |
210969 | PEAR | TOPFRUIT | CONFERENCE | 2574 | 0 | 11 | 2585 | 0.00% | 0.43% |
210974 | PEAR | TOPFRUIT | CONFERENCE | 12768 | 0 | 125 | 12893 | 0.00% | 0.97% |
210975 | PEAR | TOPFRUIT | CONFERENCE | 6636 | 0 | 81 | 6717 | 0.00% | 1.21% |
211019 | PEAR | TOPFRUIT | CONFERENCE | 72 | 0 | 0 | 72 | 0.00% | 0.00% |
211029 | KIWI | STONEFRUIT | _ | 9000 | 0 | 0 | 9000 | 0.00% | 0.00% |
211030 | PEAR | TOPFRUIT | CONFERENCE | 4392 | 0 | 0 | 4392 | 0.00% | 0.00% |
211038 | PLUM | STONEFRUIT | ANGELENO | 19440 | 375 | 500 | 20315 | 1.85% | 2.46% |
211043 | PEPPER RED | PEPPER | _ | 750 | 0 | 45 | 795 | 0.00% | 5.66% |
211051 | APPLE | TOPFRUIT | GRANNY SMITH | 6000 | 138 | 0 | 6138 | 2.25% | 0.00% |
211053 | PEAR | TOPFRUIT | CONFERENCE | 8208 | 0 | 250 | 8458 | 0.00% | 2.96% |
TOTAL | 69840 | 513 | 1012 | 71365 | 0.72% | 1.42% |
Run | Product | Category | Variety | Class 1 count | Class 2 count | Waste count | total count | Class 2 % | Waste % |
210969 | PEAR | TOPFRUIT | CONFERENCE | 2574 | 0 | 11 | 2585 | 0.00% | 0.43% |
210974 | PEAR | TOPFRUIT | CONFERENCE | 12768 | 0 | 125 | 12893 | 0.00% | 0.97% |
210975 | PEAR | TOPFRUIT | CONFERENCE | 6636 | 0 | 81 | 6717 | 0.00% | 1.21% |
211019 | PEAR | TOPFRUIT | CONFERENCE | 72 | 0 | 0 | 72 | 0.00% | 0.00% |
211029 | KIWI | STONEFRUIT | _ | 9000 | 0 | 0 | 9000 | 0.00% | 0.00% |
211030 | PEAR | TOPFRUIT | CONFERENCE | 4392 | 0 | 0 | 4392 | 0.00% | 0.00% |
211038 | PLUM | STONEFRUIT | ANGELENO | 19440 | 375 | 500 | 20315 | 1.85% | 2.46% |
211043 | PEPPER RED | PEPPER | _ | 750 | 0 | 45 | 795 | 0.00% | 5.66% |
211051 | APPLE | TOPFRUIT | GRANNY SMITH | 6000 | 138 | 0 | 6138 | 2.25% | 0.00% |
211053 | PEAR | TOPFRUIT | CONFERENCE | 8208 | 0 | 250 | 8458 | 0.00% | 2.96% |
69840 | 513 | 1012 | 71365 | 0.72% | 1.42% |
Expression -
Sum(Aggr(Sum({<Prod_Class -={'Class 1'}>}FPInnerCount),ProductionRun,Product,Prod_Class))/Sum(TOTAL<Product>Aggr(Sum({<Prod_Class>}FPInnerCount),ProductionRun,Product,Prod_Class))
This expression works perfectly if I use the product as a dimension and calculates the percentage of class 2 and waste only against the runs for that category.
However if I use the category or variety as a dimension instead I get the percentage calculated against all runs regardless of category or variety (i.e.; uses class 2 figure for cucumber and divides by total count for every run suitable for filter).
I have changed the 'Product' in the above expression for 'Category' and 'Variety' but still received the same results.
Any ideas?
Not sure how this behave for other category, but works for Cucumber:
Sum(Aggr(Sum({<Prod_Class -={'Class 1'}>}FPInnerCount),Category,ProductionRun,Prod_Class))/
Sum(DISTINCT Aggr(NODISTINCT Sum(TOTAL<Product>Aggr(Sum({<Prod_Class>}FPInnerCount),Category,ProductionRun,Prod_Class)), Category, Product))
Would you be able to share a sample? with expected output?
Please see stripped out sample attached.
A good example for working with on category is Cucumber - this only has one product within it (Cucumber whole) which means that it should return the same result for the product and the category (without filters both should return 1.91% class 2 but category currently returns 0.05%)
Not sure how this behave for other category, but works for Cucumber:
Sum(Aggr(Sum({<Prod_Class -={'Class 1'}>}FPInnerCount),Category,ProductionRun,Prod_Class))/
Sum(DISTINCT Aggr(NODISTINCT Sum(TOTAL<Product>Aggr(Sum({<Prod_Class>}FPInnerCount),Category,ProductionRun,Prod_Class)), Category, Product))
Simplifying your expression may help:
Sum(Aggr(
Sum({<Prod_Class -={'Class 1'}>} FPInnerCount)
/
Sum({<Prod_Class -={'Class 1'}>} TOTAL <Product> FPInnerCount),
ProductionRun, Product, Prod_Class))
and assuming that works, then for category:
Sum(Aggr(
Sum({<Prod_Class -={'Class 1'}>} FPInnerCount)
/
Sum({<Prod_Class -={'Class 1'}>} TOTAL <Category> FPInnerCount),
ProductionRun, Category, Prod_Class))