Problems with Excel sumifs() equivalents

[stig1984]

I am attempting to create an expression that sums data for a certain set of criteria and then calculates this as a percentage of a larger set of criteria.

In the above example I need to see the percentage of waste product on the Nectarines packed on prod_run_no 200052 per FPInnerCount as a percentage of the total FPInnerCount for all three  Prod_Class's, but without displaying Class 1

In Excel I would do this using a sumifs() (shown above) formula but I cannot get it to correctly total as a percentage in Qlikview - I can get actual quantities easily enough but I need the percentage of the whole production run (and then scaled up by day, week, month etc.) with Product and Category as dimensions.

I have tried various sum(if(  )) statements as suggested in other threads and have tried to create additional variables in the script when loading (such as LOAD sum(if(Prod_Class='Class 2',FPInnerCount)) as Class2Count) but nothing has worked.

I have been working on this now for most of the afternoon trying different ideas and am out of ideas.

Please help.

thanks

[swuehl]

You can try something like this using set analysis

=

Sum( {<Prod_Class = {'WASTE'}, PROD_RUN = {200052}>} FPInnerCount)

/

Sum( {<PROD_RUN = {200052}>} FPInnerCount)

[amayuresh · Accepted solution]

I think, this answer your question.

I have data like,

with Output as,

Dimension:

Prod_Run_No, Prod_Class

Expression:

Total=sum(FPlnnerCount)

Sub Total=Sum(TOTAL <Prod_Run_No> FPInnerCount)

% of Sub Total=sum(FPInnerCount)/Sum(TOTAL <Prod_Run_No> FPInnerCount)

Presentation Tab:

Checked "Show Partial Sums" for Dimension Prod_Class

[stig1984]

Sorry, should have been clearer.

It is each run I need to run the calculation on, not just 200052.  I need somehow to reference the production run number for every run to get the total inner count and use the inner count for just the waste to calculate the percentage for every run.

[stig1984]

many thanks

[stig1984]

Even Better -

If I do sum(FPInnerCount) / sum( {1} <Prod_Run_No> FPInnerCount) then the percenatge calculation doesn't change even if I make a selection on Prod_Class.

Thanks for all your help - my forehead can now have a rest from banging it on a brick wall.