Announcements
cancel
Showing results for
Did you mean:
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.

 Country sales Year a 100 2010 b 200 2010 c 300 2010 d 400 2010 a 800 2011 b 600 2011 c 700 2011 d 500 2011 a 100 2010 b 200 2010 c 300 2010 d 400 2010 a 800 2011 b 600 2011 c 700 2011 d 500 2011

output:

 year country sales 2010 d 800 2011 a 1600

Regards,

Sundar

1 Solution

Accepted Solutions
Master III

Check this app

11 Replies
Partner - Champion III

It is very straight forward:

1. Create a Straight Table

DONE ...

Specialist II
Author

Regards,

Sundar

Master III

Check this app

Dim: Year, Country

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

Specialist II
Author

Thank you,

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

Regards,

Sundar

Master III

Set this property, then pivot will display data

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

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.

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.

Regards,

sundar

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

Master III

Check this app

Community Browser