Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
sandrapinto
New Contributor III

Dimensions based on Total and Aggr

Hi,

I am trying to create a table / bar chart in the UI that shows Sales by a dimension built in the UI.

The dimension is meant to categorize sales by both customer and year: below 50,000 will get group 'A', other wise 'B'.

For ex: customer no. 1 with sales of 10,000 in 2017 will be in group A, while the same customer in 2018 with 60,000 will be in group B.

However, whenever I select "A", i get both 'A' AND 'B', and visa versa.

I also need the Formula to allow use of Set Analysis.

What is wrong here? 

=AGGR(IF(sum({<DataBase={'Customer Orders'}>} TOTAL <CustomerID,Year> Amount) < 50000,'A','B'),CustomerID,Year)

Thank you,

Sandra

Tags (3)
11 Replies

Re: Dimensions based on Total and Aggr

Try this

=Aggr(If(Sum({<DataBase={'Customer Orders'}, CustomerID, Year>} Amount) < 50000,'A','B'), CustomerID, Year)

sandrapinto
New Contributor III

Re: Dimensions based on Total and Aggr

thanks but same problem still

Re: Dimensions based on Total and Aggr

How about if you use this?

=Aggr(If(Sum({1<DataBase={'Customer Orders'}>} Amount) < 50000,'A','B'), CustomerID, Year)

sandrapinto
New Contributor III

Re: Dimensions based on Total and Aggr

Thanks again bur still not showing good filter.

I would add a print screen here if I new how..

Sandra

Re: Dimensions based on Total and Aggr

sandrapinto
New Contributor III

Re: Dimensions based on Total and Aggr

Thanks!

Here is the print screen

I have selected A but still the table shows B as well.

Sandra

Capture.PNG

sandrapinto
New Contributor III

Re: Dimensions based on Total and Aggr

by the way,

if I remove the 1 from the set analysis of the MEASURE, I still get the exact same result

Sandra

Re: Dimensions based on Total and Aggr

What is the expression for your filter?

sandrapinto
New Contributor III

Re: Dimensions based on Total and Aggr

the filter is the same as the Dimenstion column - same expression.

=Aggr(If(Sum({1<DataBase={'Customer Orders'}>} Amount) < 50000,'A','B'), CustomerID, Year)