Count and sum function in Qliksense

[SK28]

Hi,

I have a raw data,here it is

Leader Customer, Value
L1,  A, 20
L1,A, 30

L1, P, 120

L1, A,100

L1,B,150

L1, Ba, 40

L2,B, 100
L2,B, 150

L2,G, 40

L3,  C,100

L3,  C,150

L3,H, 100 

L4,D,80

L4, D, 60

L5, D, 60

L5, F,70 etc etc

So the requirement here is I want to get the count of  CUSTOMER whose value is greater than 100.

If I'm using Count ({< value ={'>100'} >} customer)

it's not working properly, it's giving me different results.

it's Counting those records which are more than 100

For now I'm using the Resident load.

here you go

Temp:

Load *;

where Value > 100;

Load customer, sum(value) 

resident  Main_table

Again I'm mapping the leader to the main table,which has the raw information.

the required out put is who are >100

Leader , Count of Customer

L1, 3 

L2, 1

L3, 2

L4, 1,

L5, 0

The requirement here is I need to get those counts

and use those them as a thresold limit

Can we have any other better solution please?

this is becoming Complicated.

I have to do same for other Levels.

Like programs, portfolios.

The model is becoming Complicated.

Can we achieve this using aggr() or any other functions? please.

[ogster1974]

Will help with your set analysis if your field names are named the same as your data and you use " instead of '.

Looks like its working fine using your example.

[SK28]

Not the distinct count.

It should be summing up for Leader level and get those counts

example for L1

It should get me this.

Count ( {<Sum(value) > 100 >} customers)

to get this I'm using the resident load and mapping it to the main table again,

Can we achieve this, in simple fashion?

[ogster1974 · Accepted solution]

Something like 

Create a key in your script for your Aggr level.

Load *,
Customer&'|'&Leader as Key
;
Load * Inline
[
Leader, Customer, Value
L1, A, 20
L1,A, 30
L1, P, 120
L1, A,100
L1,B,150
L1, Ba, 40
L2,B, 100
L2,B, 150
L2,G, 40
L3, C,100
L3, C,150
L3,H, 100
L4,D,80
L4, D, 60
L5, D, 60
L5, F,70
];

Then use it like this

Count(Distinct {<Key={"=Aggr(Sum(Value), Key)>100"}>} Customer)

[SK28]

thank you