Get maximum value (Set Analysis)

[#1]

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!

[#2]

Hi Jagan,

I'm currently testing the script!

[#3]

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)

[jagan]

Hi,

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

Regards,

Jagan.

[#5]

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

];

[jagan · Accepted solution]

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.

[#7]

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

Thanks,

Ram

[#8]

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!

[jagan]

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.

[#10]

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!

[#11]

Hi Jagan,

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

Set analysis question