topic Re: Problems with Excel sumifs() equivalents in QlikView

Problems with Excel sumifs() equivalents

stig1984 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Problems with Excel sumifs() equivalents

swuehl — Fri, 18 Mar 2016 18:55:43 GMT

You can try something like this using set analysis

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

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

Re: Problems with Excel sumifs() equivalents

amayuresh — Mon, 21 Mar 2016 07:34:47 GMT

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

Re: Problems with Excel sumifs() equivalents

stig1984 — Mon, 21 Mar 2016 12:48:24 GMT

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.

Re: Problems with Excel sumifs() equivalents

stig1984 — Mon, 21 Mar 2016 13:02:59 GMT

many thanks

Re: Problems with Excel sumifs() equivalents

stig1984 — Mon, 21 Mar 2016 13:19:41 GMT

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.