Conditional Sum

[Anonymous]

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:

Delivery Number (Key)	Supplier	Credits
1	A	80
2	B	50
3	C	20
4	A	100
5	A	40
6	C	90
7	B	20
8	A	10

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]

Try this

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

[Anonymous]

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]

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))