topic Expression help in QlikView

Expression help

sundarakumar — Sat, 16 May 2015 10:09:41 GMT

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

Thanks in advance

Regards,

Sundar

Re: Expression help

petter — Sat, 16 May 2015 10:13:04 GMT

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 ...

Re: Expression help

sundarakumar — Sat, 16 May 2015 10:20:07 GMT

Thanks for the reply,

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

Regards,

Sundar

Re: Expression help

anbu1984 — Sat, 16 May 2015 12:07:28 GMT

Check this app

Dim: Year, Country

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

Re: Expression help

sundarakumar — Sat, 16 May 2015 12:28:01 GMT

Thank you,

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

Regards,

Sundar

Re: Expression help

anbu1984 — Sat, 16 May 2015 12:38:21 GMT

Set this property, then pivot will display data

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

Re: Expression help

ToniKautto — Sat, 16 May 2015 12:38:21 GMT

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.

Re: Expression help

sundarakumar — Sat, 16 May 2015 13:01:35 GMT

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

Re: Expression help

sundarakumar — Sat, 16 May 2015 13:04:10 GMT

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

Re: Expression help

anbu1984 — Sat, 16 May 2015 14:59:16 GMT

Check this app

Re: Expression help

sundarakumar — Sat, 16 May 2015 15:19:06 GMT

Anbu,

This is extremely awesome stuff....

Thanks a lot ...

Regards,

-Sundar

Re: Expression help

anbu1984 — Wed, 20 May 2015 11:58:39 GMT

This is better expr than Concat()

FirstSortedValue(Country,Aggr(-Sum(sales) ,Country,Year,prod))