Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an issue where I am attempting to count the number of Users that have an total amount per week over a given figure (9 in this example)
This is my very simple test script:
LOAD * INLINE [
User, Week, Amount
1, 1, 1
2, 1, 3
3, 2, 2
1, 1, 10
1, 2, 9
3, 2, 15
2, 2, 7
4, 1, 3
1, 3, 5
1, 3, 6
2, 2, 6
3, 2, 1
4, 1, 5
];
and I can create a pivot showing me the "sum(Amount)" and "if(sum(Amount)>9,1,0)" as expressions, but I would then like to count the amount of Distinct Users per week that meet the true portion of "if(sum(Amount)>9,1,0)"
This can be in another pivot with the week as the dimension.
I'm pretty sure I need an aggr expression, but I can't for the life of me figure it out.
File attached:
May be try this
Dimension
Week
Expression
Sum(Aggr(if(sum(Amount)>9,1,0), User, Week))
May be try this
Dimension
Week
Expression
Sum(Aggr(if(sum(Amount)>9,1,0), User, Week))
Hi,
Maybe:
Week | Count(DISTINCT Aggr(if(Sum(Amount)>9,User),User,Week)) |
---|---|
1 | 1 |
2 | 2 |
3 | 1 |
Cheers
Andrew
Thank you so much, worked perfectly. Now I just need to work out how. The other suggestion worked too but looks more complicated.