topic Re: Set analysis max value in QlikView

Set analysis max value

help4qv123 — Wed, 13 May 2015 05:03:37 GMT

HI,

i have 3 columns..

By default i need a bar chart ,if u select a key there is some max version for that key..i need the value of that version for all keys.

i have attached a sample for u..plz refer to that !

Re: Set analysis max value

Wed, 13 May 2015 05:21:57 GMT

You are using Sum(value), instead try max(value).

Re: Set analysis max value

MayilVahanan — Wed, 13 May 2015 05:26:00 GMT

Try like this

=FirstSortedValue(value , - version)

Hope it helps

Re: Set analysis max value

help4qv123 — Wed, 13 May 2015 05:31:11 GMT

hi,

but i have an expression like count(value)...where max cant be applied

Re: Set analysis max value

help4qv123 — Wed, 13 May 2015 05:38:07 GMT

thank you. but here in my sample it is value which picks max value..but i have order column..i need the count of order for each key with max version.

my expression is count(ordr)

Re: Set analysis max value

Wed, 13 May 2015 05:42:30 GMT

I think MayilVahanan‌ had given correct solution.

Re: Set analysis max value

help4qv123 — Wed, 13 May 2015 05:49:27 GMT

yes its right, but it doesnt work in count...suppose my expression is count(ordr)...which need to pick the max version. then what wil be the expression

Re: Set analysis max value

Wed, 13 May 2015 06:34:20 GMT

something like this may be:

FirstSortedValue(aggr(count(value),version),-version)

Re: Set analysis max value

help4qv123 — Wed, 13 May 2015 07:09:11 GMT

no bro its not working

Re: Set analysis max value

jpenuliar — Wed, 13 May 2015 10:09:22 GMT

max({<version = {'=Max(version)'}>}value)

Re: Set analysis max value

jpenuliar — Wed, 13 May 2015 10:17:00 GMT

almost

Re: Set analysis max value

help4qv123 — Wed, 13 May 2015 10:20:26 GMT

no bro..the expression which u have sent is for max value...i need the count of oder count(oder) for each key with its max version,,,for eg: for key 20130101 max version is 2 and count of oder is 3...i need dis for all keys... !

Re: Set analysis max value

help4qv123 — Wed, 13 May 2015 11:15:47 GMT

bro any output on my post

Re: Set analysis max value

jpenuliar — Wed, 13 May 2015 11:38:45 GMT

you're better off manipulating the source table.

I derived the max version for each year and drop rows not related to max year:

data:
load * Inline
[
key, version, value, oder
20130102, 4, 7, oredr4
20110101, 3, 3, order1
20120201, 3, 76, order3
20130102, 3, 1, oredr4
20110101, 2, 11, order1
20120201, 2, 65, oder2
20130101, 2, 5, oder3
20130102, 2, 2, oredr4
20110101, 1, 12, order1
20120201, 1, 34, order1
20130101, 1, 6, oder3
20130102, 1, 4, order3
];

tab:
Join
Load key,
max(version) as MaxVersion
Resident data
Group by key;

data1:
NoConcatenate
load
key,
version,
value,
oder,
MaxVersion
Resident data
Where version = MaxVersion;

drop Table data;

Re: Set analysis max value

jpenuliar — Wed, 13 May 2015 11:40:39 GMT

resulting table :

Re: Set analysis max value

jpenuliar — Wed, 13 May 2015 11:41:11 GMT

I suppose this app is a KPI thing?

Re: Set analysis max value

ToniKautto — Wed, 13 May 2015 11:52:12 GMT

You can not resolve that with a set expression, since you are trying to alter the set row by row.

In your example I think =max(aggr(only(value), key, value)) gives the result you have specified

Re: Set analysis max value

help4qv123 — Wed, 13 May 2015 12:46:46 GMT

it gives me the same values for the max version

i need the count(oder) with dimension as key...and for each key there is max version...select max version for 1 key..then see there count of orders

Re: Set analysis max value

help4qv123 — Wed, 13 May 2015 12:54:23 GMT

Thank you...lets c it could be done in front end

Re: Set analysis max value

jpenuliar — Wed, 13 May 2015 13:11:18 GMT

add Flag to rows:

data1:
NoConcatenate
load
key,
version,
value,
oder,
MaxVersion,
If(version = MaxVersion,1,0) as MaxVersionValues
Resident data
;

so now you dont have to drop the non-related rows. table looks below: