Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

robinsarkar
New Contributor

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

Re: Sum, Group By and then Count

Try this

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

6 Replies

Re: Sum, Group By and then Count

Try this

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

robinsarkar
New Contributor

Re: Sum, Group By and then Count

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])

Re: Sum, Group By and then Count

This

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

robinsarkar
New Contributor

Re: Sum, Group By and then Count

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.

robinsarkar
New Contributor

Re: Sum, Group By and then Count

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?

Re: Sum, Group By and then Count

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])

Community Browser