Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Test if Sum of Revenue by Account is more than 10K

I create a new dimension in a pivot table. I would like a different behavior depending on whether the sum of revenues over Accounts is more or less than 10K. For now I came up with

If(AGGR(SUM(Revenue),[Account])>10000,'More','Less')

Which seems not to be working...

What is the correct syntax ?

Account is a dimension in the pivot table.

10 Replies
alexandros17
Partner - Champion III
Partner - Champion III

Try this:

If(Sum(AGGR(SUM(Revenue),[Account]))>10000,'More','Less')


let me know

Not applicable
Author

brings "Error in calculated Dimension"

alexandros17
Partner - Champion III
Partner - Champion III

Could you attach the document?

Not applicable
Author

If((AGGR(SUM(Revenue),[Account]))>10000,'More','Less')


is working

Not applicable
Author

This is working:

=If(AGGR(Sum(Revenue),Account)>10,'More','Less')

Now, I would like to replace [Account] by another calculated dimension in the pivot table. I am replacing Account directly by the expression of this other calculated dimension, which is a long pick(match...)). Result is calculated, but wrongly gives 'Less' everywhere.

What are potential errors ? What would be the right approach ?

Thanks in advance

Not applicable
Author

Is AGGR(Sum(Revenue),Field) calculating the subtotal of SUM Revenue for each value of the field ? Ex with data

FIELD   REVENUE
Val1     10

Val1     5

Val2     1

I would like to get

FIELD   AGGR(Sum(Revenue),Field)
Val1     15

Val1     15

Val2     1

Is it the case indeed?

its_anandrjs

Try like

AGGR(Sum(TOTAL Revenue),Field)

mahesh_agrawal
Creator
Creator

Hi,

Try this expression.

Aggr(if(sum(Revenue)>10K,'More','Less'),[Acc No])

whenever we use IF() in dimension,we have to use Aggr() on the IF().

Not applicable
Author

this expression give Null() for all rows...