topic Re: Max used in an expression in QlikView

Max used in an expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Max used in an expression

sushil353 — Mon, 11 Jul 2016 08:30:24 GMT

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

Re: Max used in an expression

Mon, 11 Jul 2016 08:30:55 GMT

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/max.htm

Re: Max used in an expression

rubenmarin — Mon, 11 Jul 2016 08:40:34 GMT

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/pick.htm

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

Re: Max used in an expression

Anonymous — Mon, 11 Jul 2016 09:23:16 GMT

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

Re: Max used in an expression

sushil353 — Mon, 11 Jul 2016 09:29:08 GMT

got it,

Please find attached.

HTH

Re: Max used in an expression

varshavig12 — Mon, 11 Jul 2016 09:32:32 GMT

Re: Max used in an expression

Anonymous — Mon, 11 Jul 2016 09:38:41 GMT

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

Re: Max used in an expression

varshavig12 — Mon, 11 Jul 2016 09:48:40 GMT

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

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

Re: Max used in an expression

tamilarasu — Mon, 11 Jul 2016 09:53:52 GMT

Hi Laura,

Try like below,

Dimension:

Account

Expression

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

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