Max used in an expression

[Anonymous]

Hi,

I have the following data loaded into a table in Qlikview..  for each call made per code it simply totals it.  so in Qlikview I need to only pick up the "Max" count.

I have tried a few formulas but saw this simple one posted on community but it does not return the correct result.  any help appreciated

=Pick(CallCode= 'HR Calls'',max(Count))

Account	RowCount	CallCode	Count
100000	1	HealthCalls	1
100002	1	HR Calls	2
100002	1	HR Calls	2
100050	1	HR Calls	1
100050	1	HealthCalls	3
100050	1	HealthCalls	3
100050	1	HealthCalls	3

In qlikview  there is only one line per account so it will end up looking like this:-

Account	HealthCalls used	HR Calls used
100000	1	0
100002	0	2
100050	3	1

[sushil353]

Hi,

I am not clear about the expected output.

But using this expression: =Aggr(Max(Count),Account)

the output will be like below:

Let me know if you are looking for something else

HTH

[#3]

Because qlikview is dynamic, when u use a 'max' it only calculate the data area you had selected. To find the only "max" you can do some tricks in the loading script just like SQL!

Ex:

Max:

LOAD Customer, Max(UnitSales) as MyMax Resident Temp Group By Customer;

FYR: https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/AggregationFunctions/m...

[rubenmarin]

Hi, I'm not sure of your requirements but Pick() has another use: https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/ConditionalFunctions/p...

If you make a pivot table with account as horizontal dimension and CallCode as vertical dimension, the expression can be just "=Max(Count)"

[Anonymous]

thanks Sushil.  I need the results to look like this.  I.e. rather than one row per code like you have.   does this make sense?

Account	HealthCalls used	HR Calls used
100000	1	0
100002	0	2
100050	3	1

[sushil353]

got it,

Please find attached.

HTH

[varshavig12]

[Anonymous]

its now giving the same value in both Call types.  As I have added separate expressions for the call types,  can I add CallCode = "HealthCalls used"   ?

Account	HealthCalls used	HR Calls used
100000	1	1
100002	2	2
100050	3	3

[varshavig12]

Aggr(Max({<CallCode={'HealthCalls'}>}Count),Account)

Aggr(Max({<CallCode={'HR Calls'}>}Count),Account)

[tamilarasu]

Hi Laura,

Try like below,

Dimension:

Account

Expression

1. Max({<CallCode={'HealthCalls'}>}Count)

2. Max({<CallCode={'HR Calls'}>}Count)