Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
sundarakumar
Specialist II
Specialist II

Expression help

Hi All,

Below is the sample data set, I want to create a straight table as shown in output.

Requirement: the country name which made the max sale for every year.

CountrysalesYear
a1002010
b2002010
c 3002010
d4002010
a8002011
b6002011
c 7002011
d5002011
a1002010
b2002010
c 3002010
d4002010
a8002011
b6002011
c 7002011
d5002011

output:

yearcountrysales
2010d800
2011a1600

Thanks in advance

Regards,

Sundar

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

11 Replies
petter
Partner - Champion III
Partner - Champion III

It is very straight forward:

1. Create a Straight Table

2. Add Year as dimension

3. Add Only(Country) as expression

4. Add Max(sales) as expression

DONE ...

sundarakumar
Specialist II
Specialist II
Author

Thanks for the reply,

Attached the sample app, can you please help me with what am misssing?

Regards,

Sundar

anbu1984
Master III
Master III

Check this app

Dim: Year, Country

Expr: If(Sum(sales)=Max( TOTAL<Year> Aggr(Sum(sales),Country,Year)),Sum(sales))

sundarakumar
Specialist II
Specialist II
Author

Thank you,

this is working as expected, but y is this not working in a pivot table?

Regards,

Sundar

anbu1984
Master III
Master III

Set this property, then pivot will display data

Chart Properties --> Presentation --> Check "Always fully expanded"

ToniKautto
Employee
Employee

Why do you want to move it to a pivot table? Your initial questions seems very well suited for a straight table presentation.

Please attach an example of what your pivot table looks like, so we can understand how you have done the pivot table.

sundarakumar
Specialist II
Specialist II
Author

Hi Toni,

The sample that I have posted above has just 2 dimensions, but in actual scenario there are three, I was hoping that i can replicate the result for three dimensions once i get an idea here

If(Sum(sales)=Max( TOTAL<dim1,dim2> Aggr(Sum(sales),dim1,dim2,dim3)),Sum(sales))

I was trying to get one dimension horizontal and another vertical so wanted to switch over to Pivot.

Please find attached the sampe excel with the expected output.

Thanks in advance.

Regards,

sundar

sundarakumar
Specialist II
Specialist II
Author

Anbu,

Thanks for the reply, Attached the sample data and output required. Sorry for the trouble i thought i will be able to replicate the sample to my scenario

Thanks again

Regards,

Sundar

anbu1984
Master III
Master III

Check this app