Count a sum of Amount per week

[quilldew]

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:

[sunny_talwar · Accepted solution]

May be try this

Dimension

Week

Expression

Sum(Aggr(if(sum(Amount)>9,1,0), User, Week))

[effinty2112]

Hi,

Maybe:

Week	Count(DISTINCT Aggr(if(Sum(Amount)>9,User),User,Week))
1	1
2	2
3	1

Cheers

Andrew

[quilldew]

Thank you so much, worked perfectly. Now I just need to work out how. The other suggestion worked too but looks more complicated.