Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gsiva1220
Contributor II
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
uacg0009
Partner - Specialist
Partner - Specialist

How about this :

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

I tried this in my qvw, it works.

View solution in original post

15 Replies
Anil_Babu_Samineni

May be this?

Max(Cost,2)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
uacg0009
Partner - Specialist
Partner - Specialist

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
Contributor II
Contributor II
Author

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

gsiva1220
Contributor II
Contributor II
Author

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

uacg0009
Partner - Specialist
Partner - Specialist

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
Contributor II
Contributor II
Author

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

uacg0009
Partner - Specialist
Partner - Specialist

How about this :

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

I tried this in my qvw, it works.

uacg0009
Partner - Specialist
Partner - Specialist

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

gsiva1220
Contributor II
Contributor II
Author

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