Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
gsiva1220
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:

 

MonthCost
Jan10
Jan11
Jan11
Jan13
Jan12
Feb11
Feb12
Feb12
Feb12
Feb13

Output :

 

Monthmax( aggr( count(Cost),Month,Cost))Max Cost Value
Jan211
Feb312

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

1 Solution

Accepted Solutions
Partner
Partner

Re: MAX COUNT and its value in PIVOT Table

How about this :

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

LOAD * INLINE [

  Month, Cost

    Jan, 10

    Jan, 11

    Jan, 11

    Jan, 13

    Jan, 12

    Feb, 11

    Feb, 12

    Feb, 12

    Feb, 12

    Feb, 13

];

T2:

NoConcatenate

LOAD Month,Cost,

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)

MAX COUNT and its value in PIVOT Table.PNG

Thanks

Aiolos

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

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

gsiva1220
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
Partner

Re: MAX COUNT and its value in PIVOT Table

How about this :

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

I tried this in my qvw, it works.

Partner
Partner

Re: MAX COUNT and its value in PIVOT Table

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

gsiva1220
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