topic Re: Get maximum value (Set Analysis) in QlikView

Get maximum value (Set Analysis)

Mon, 23 Jun 2014 15:16:31 GMT

Hi all,

I have a question regarding the following conditions:

I have a table:

Letter	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14
A	100	200	300	400	500	600
B		100	200	300	400	500
C			600	700	800	900

I need to create a table where I have to output the latest value (latest value is base on Letter (A<B<C<D,.....)

The output should be:

	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14
Qty	100	100	600	700	800	900

How do I achieve this? Please take note that my selection of letters can differ. I select A and C it should output something like this:

	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14
Qty	100	200	600	700	800	900

The month selected also changes. How to achieve this? How can I only get the latest value?

Thanks in advance!

Re: Get maximum value (Set Analysis)

vgutkovsky — Mon, 23 Jun 2014 21:52:04 GMT

You're talking about in the UI, not data model, I assume. So the above is a pivot table with month-year pivoted across the top, right? If so, the easiest way would probably be: firstsortedvalue(Qty,-ord(Letter))

Regards,

Vlad

Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 01:13:10 GMT

PFA.

Thanks,

Ram

Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 01:25:45 GMT

Hi Ram,

Thanks for the reply! how about if I have a computation inside the formula?

for example: sum(Qty)

Thanks!

Re: Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 01:58:16 GMT

There you go. If you're happy can you mark mine as the correct answer.

Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 02:30:20 GMT

Hi Ram,

Thanks for the reply although base on the app you have given. Look @ Jun-14. You have summed up all for Jun-14. I only need the letter C which is the latest value (600). What I'm saying is. ex:

I have many items for Jun-14 under letter C

Month Letter QTY

Jun-14 C 500

Jun-14 C 100

i need to sum it up so for Jun-14 = 600.

I need to output 600 not the sum of all the values under Jun-14

Re: Get maximum value (Set Analysis)

jagan — Tue, 24 Jun 2014 04:22:10 GMT

Hi,

Try this script

Temp:

CrossTable(Month, Qty)

load * inline

[

Letter, Apr-14, May-14, Jun-14, Jul-14, Aug-14, Sep-14

A ,100 ,200, 300, 400, 500, 600

B ,0 ,0,100, 200 ,300 ,400 ,500

C ,0,0 ,600 ,700, 800, 2500

];

Data:

Load

If(Previous(Month) <> Month OR RowNo() = 1, 1, 0) AS IsLatest

RESIDENT Temp

WHERE Qty > 0

Order by Month, Letter Desc;

Drop table Temp;

Use this as expression in chart

Sum({<IsLatest={1}>} Qty)

Regards,

Jagan.

Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 04:47:11 GMT

Hi Lyn,

Please find attached.

Thanks,

Ram

Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 05:22:21 GMT

Hi Ram,

Thanks for this. Almost there but I need to output not the highest but the latest Letter.

Month

Apr-14

May-14

Jun-14

Jul-14

Aug-14

Sep-14

Letter

150

250

350

100

600

450

200

700

550

300

800

650

400

2500

In the example you have given, for Apr-14 output should be C not A. because it is the latest letter.

Re: Get maximum value (Set Analysis)

jagan — Tue, 24 Jun 2014 05:27:54 GMT

Hi,

Did you tried my script which I attached?

Regards,

Jagan.

Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 06:02:01 GMT

Hi Jagan,

I'm currently testing the script!

Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 06:06:40 GMT

Hi Jagan,

Upon testing, It works fine but it doesn't show show of the values I need. ex: I select Letter: A and C.

It shows all the latest but it's missing some dates which needs to be still shown. Please try to load this data:

LOAD * INLINE [

Month, Letter, Qty

Jan-14, A, 100

Feb-14, A, 200

Mar-14, A, 100

Apr-14, A, 300

Feb-14, B, 100

Mar-14, B, 200

Apr-14, B, 70

May-14, B, 50

Mar-14, C, 10

Apr-14, C, 20

May-14, C, 10

Jun-14, C, 5

];

If you select A and C, it will only show Jan, Mar, Apr, May, Jun. It should also show Feb (but it is not tag as latest)

Re: Get maximum value (Set Analysis)

jagan — Tue, 24 Jun 2014 06:19:01 GMT

Hi,

In Feb-14, B is latest, since you select only A and C, that is why B is missing.

Regards,

Jagan.

Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 06:26:22 GMT

Hi Jagan,

Please see above inline:

LOAD * INLINE [

Month, Letter, Qty

Jan-14, A, 100

Feb-14, A, 200

Mar-14, A, 100

Apr-14, A, 300

Feb-14, B, 100

Mar-14, B, 200

Apr-14, B, 70

May-14, B, 50

Mar-14, C, 10

Apr-14, C, 20

May-14, C, 10

Jun-14, C, 5

];

Re: Get maximum value (Set Analysis)

jagan — Tue, 24 Jun 2014 06:34:02 GMT

Hi,

We are arriving the flag in script, the flag is marked for B in Feb-14, since you have selected A and C, you dont have B in the valid list. If you want like this based on selections then you do this in UI level.

Try like this

LOAD * INLINE [

Month, Letter, Qty

Jan-14, A, 100

Feb-14, A, 200

Mar-14, A, 100

Apr-14, A, 300

Feb-14, B, 100

Mar-14, B, 200

Apr-14, B, 70

May-14, B, 50

Mar-14, C, 10

Apr-14, C, 20

May-14, C, 10

Jun-14, C, 5

];

Dimension: Month

Expression: Sum(Aggr(If(MaxString(TOTAL<Month>Letter) = Letter,Sum(Qty)), Month, Letter))

Regards,

Jagan.

Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 06:49:27 GMT

Hi Jagan, Can you please elaborate on your expression? Looks an interesting one.

Thanks,

Ram

Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 06:58:54 GMT

Hi Jagan,

This one works like a charm! But I have another question. What if I have 2 kinds of values. Let say I have Qty and Revenue. I tried this one but it sums up the 2 values. As what Ram said, kindly elaborate the expression.

Thanks!

Re: Get maximum value (Set Analysis)

jagan — Tue, 24 Jun 2014 07:31:48 GMT

Hi,

Use this for getting Revenue

Dimension: Month

Expression: Sum(Aggr(If(MaxString(TOTAL<Month>Letter) = Letter,Sum(Revenue)), Month, Letter))

Explanation:

MaxString(TOTAL<Month>Letter) - will give you the max letter in the month

Letter - will give you the current row Letter Value

If(MaxString(TOTAL<Month>Letter) = Letter,Sum(Qty)) - If Max Letter of the month = Letter of current record then we are getting Sum(Qty)

Aggr() - Will group the records by given fields, here we are using Month and Letter

Hope this helps you.

Regards,

Jagan.

Re: Get maximum value (Set Analysis)

Tue, 24 Jun 2014 07:55:35 GMT

Hi Jagan,

Thanks! This is only thing I was looking for. Getting the maximum value in every column. (in this case the Month). So getting the maximum letter per Month. Again thanks!

Re: Get maximum value (Set Analysis)

Sat, 27 Sep 2014 04:35:14 GMT

Hi Jagan,

I have again a problem just like this, can you look into this?

Set analysis question