Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
quilldew
Creator
Creator

Count a sum of Amount per week

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:

1 Solution

Accepted Solutions
sunny_talwar

May be try this

Dimension

Week

Expression

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

View solution in original post

3 Replies
sunny_talwar

May be try this

Dimension

Week

Expression

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

effinty2112
Master
Master

Hi,

Maybe:

Week Count(DISTINCT Aggr(if(Sum(Amount)>9,User),User,Week))
11
22
31

Cheers

Andrew

quilldew
Creator
Creator
Author

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