topic Re: Conditional Sum in QlikView

Conditional Sum

Anonymous — Tue, 15 May 2018 12:41:29 GMT

Hi community!

Sorry for this probably very stupid question: I am a Qlikview beginner and am trying to solve the following problem:

I have a list with deliveries from different suppliers and credits for each delivery, like this:

Now, I would like to display bar graphs showing the "average credits" for each supplier.

=> Diagram Formula: Sum(Credits)/Count(Credits) => Works!

Now, I would like to narrow down the bar graph, so that it only show the average credits for suppliers that shipped, e.g. 100 times.

I tried various set analysis versions, including aggr functions, but none seemed to work correctly.

My best bet would have been this one:

sum({<Credits={"=count(Credits)>100"}>} Credits) /count(Credits)

but also without success.

I also tried to use the conditional option in properties:

sunny_talwar — Tue, 15 May 2018 16:04:07 GMT

Try this

Sum({<Supplier = {"=Count(Credits) > 100"}>} Credits)/Count(Credits)

Anonymous — Wed, 16 May 2018 08:27:11 GMT

Hi Sunny,

thank you for your hint. It was not completely what I needed, but the key was to use "Supplier" instead of "Credits" within the condition.

=> Here is the working code:

Sum({<Supplier = {"=Count(Credits) > 100"}>} Credits)/Count({"=Count(Credits) > 100"} Credits)

Best regards

Andreas

Anonymous — Wed, 16 May 2018 08:30:17 GMT

I was quite successful the last few hours and even found another, even more simple code doing the same:

if(count(Credits)>100,sum(Credits)/count(Credits))