Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sum, Group By and then Count

I have a raw data table below which has multiple lines of same orders and revenue associated to it. I first want to Sum all the revenue based on the Orders and then want to count all the orders that are above says $500 I came up with below expression but doest seem to be working

COUNT ({<Aggr(SUM(Revenue,[Order No])={">500"}>}[Order No])

Below is the Example 

Order No

Revenue

3703

$     500.00

3703

$       50.00

3703

$       56.00

3703

$       20.00

3805

$     460.00

3805

$ 8,000.00

3805

$       23.00

3805

$     210.00

3806

$     499.00

3811

$       20.00

3811

$     100.00

3811

$     213.00

First I need to sum up all the Revenue by their Orders

This will Eventually look like this

Order No

Revenue

3703

$     626.00

3805

$ 8,693.00

3806

$     499.00

3811

$     333.00

And the The orders Above $500 = 2

How do I get these 2 steps in one expression? I tried the one above but doesn’t work.

1 Solution

Accepted Solutions
sunny_talwar

Try this

Count(DISTINCT {<[Order No] = {"=Sum(Revenue) > 500"}>} [Order No])

View solution in original post

6 Replies
sunny_talwar

Try this

Count(DISTINCT {<[Order No] = {"=Sum(Revenue) > 500"}>} [Order No])

Anonymous
Not applicable
Author

Thanks Man! That worked. another sub question on that If i need get a count of Revenue in between 200-500 should i use the below syntax

Count(DISTINCT {<[Order No] = {"=Sum(Revenue) <500"} and {"=Sum(Revenue) >200"} >} [Order No])

sunny_talwar

This

Count(DISTINCT {<[Order No] = {"=Sum(Revenue) >200 and Sum(Revenue) <500"}>} [Order No])

Anonymous
Not applicable
Author

That worked too, Thanks Again!

Since I'm new I still struggle with set analysis and especially with expressions that needs two or three steps, Do you have any recommendation on books or sites, that will help me get this basics right.

Anonymous
Not applicable
Author

Sunny,

I had another Question, Eventually I'm trying to create a Histogram that shows orders, >100, between 100-200, 200-500 and >500. whats the best way to do it ..Do I create four separate expressions?

sunny_talwar

Created a calculated dimension like this

Calculated Dimension

Aggr(

If(Sum(Revenue) < 100, Dual('<100', 1),

     If(Sum(Revenue) <  200, Dual('100-200', 2),

          If(Sum(Revenue) < 500, Dual('200-500', 3), Dual('>500', 4)))), [Order No])

Expression

Count(DISTINCT [Order No])