Solved: Get maximum value (Set Analysis) - Qlik Community

Report Inappropriate Content · ‎2014-06-23

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!

jagan · ‎2014-06-24

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.

View solution in original post

vgutkovsky · ‎2014-06-23

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

Report Inappropriate Content · ‎2014-06-23

PFA.

Thanks,

Ram

Report Inappropriate Content · ‎2014-06-23

Hi Ram,

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

for example: sum(Qty)

Thanks!

Report Inappropriate Content · ‎2014-06-23

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

Report Inappropriate Content · ‎2014-06-23

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

jagan · ‎2014-06-24

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.

Report Inappropriate Content · ‎2014-06-24

Hi Lyn,

Please find attached.

Thanks,

Ram

Report Inappropriate Content · ‎2014-06-24

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

A

B

C

A

C

A

B

C

A

B

C

A

B

C

A

B

C

150

25

250

35

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.

jagan · ‎2014-06-24

Hi,

Did you tried my script which I attached?

Regards,

Jagan.