Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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.