# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
New Contributor II

## MAX COUNT and its value in PIVOT Table

Hi ,

I facing an issue to know the value which has max count, let me explain you by example.

Input:

 Month Cost Jan 10 Jan 11 Jan 11 Jan 13 Jan 12 Feb 11 Feb 12 Feb 12 Feb 12 Feb 13

Output :

 Month max( aggr( count(Cost),Month,Cost)) Max Cost Value Jan 2 11 Feb 3 12

I can able to achieve the max count but I am facing an Issue finding the max cost value. Can you please let me know how can I achive this? Thank in advance.

Regards,

Siva

Tags (6)
1 Solution

Accepted Solutions
Partner

## Re: MAX COUNT and its value in PIVOT Table

expression : FirstSortedValue(distinct Cost,-aggr( count(Cost),Month,Cost),1)

I tried this in my qvw, it works.

15 Replies

## Re: MAX COUNT and its value in PIVOT Table

May be this?

Max(Cost,2)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner

## Re: MAX COUNT and its value in PIVOT Table

Hi Siva,

If you can accepted that adding some scripts in back-end, then I found a method, but I think it's not good, I'm trying to find another way.

My Script：

T1:

Month, Cost

Jan, 10

Jan, 11

Jan, 11

Jan, 13

Jan, 12

Feb, 11

Feb, 12

Feb, 12

Feb, 12

Feb, 13

];

T2:

NoConcatenate

Count(Cost) as num

Resident T1

Group by Month,Cost;

Left Join(T1)

LOAD Month,Cost,max(num) as num Resident T2 Group By Month,Cost;

DROP Table T2;

The expression : FirstSortedValue(distinct Cost,-num,1)

Thanks

Aiolos

New Contributor II

## Re: MAX COUNT and its value in PIVOT Table

Thanks for your reply Anil. It is showing as below which is incorrect. Please find the attachement.

New Contributor II

## Re: MAX COUNT and its value in PIVOT Table

Thanks for your reply uacg0009As you said I need in Pivot table script it self. Adding another table will become performance Issue in our case.

Regards,
Siva

Partner

## Re: MAX COUNT and its value in PIVOT Table

Actually you can see my data model, I didn't add table, I just add one column finally.

But It will takes some times to make this column, so if your data is too large, it will affect your loading time I think.

If you can't accept this method, maybe I need to try to use another front-end way to make it.

Thanks.

Aiolos

New Contributor II

## Re: MAX COUNT and its value in PIVOT Table

Yes Aiolos, Table has some millions of data adding another field will cause performance. If we don't have any front end way then I can go with backend script.

Thanks,

Siva

Partner

## Re: MAX COUNT and its value in PIVOT Table

expression : FirstSortedValue(distinct Cost,-aggr( count(Cost),Month,Cost),1)

I tried this in my qvw, it works.

Partner

## Re: MAX COUNT and its value in PIVOT Table

You can see the attachment, using the expression like I said.

New Contributor II

## Re: MAX COUNT and its value in PIVOT Table

Seems like it is working, I will do further testing and let you know. Thank you uacg0009