How to show rows from last and on ?

[yanivvl0]

Hi to all ,

i have table of programs, for each program can be many status , i need to show all the status from IS_LAST row and on (accending)  ,

for example : to show status 20-70 BUT not status 10 :

jow can i do that in straight table ( or pivot ) ?   THANKS.

[Anil_Babu_Samineni]

This?

Sum({<status = {">= 20 <=70"}>} program)

OR

Sum({<status -= {'10'}>} program)

[yanivvl0]

for this case its ok , but i need for all cases because IS_LAST can be at any status :

[Anil_Babu_Samineni]

Means?

[Peter_Cammaert]

If these aren't dynamically calculated in the UI, you can easily set a flag in your Load script.  Imagine your table is called "Data". Then the following could act as a guideline:

LEFT JOIN(Data)

LOAD program,

     status as LastStatus

RESIDENT Data

WHERE IS_LAST = 1;

NewData:

LOAD *,

     IF (status >= LastStatus, 1, 0) AS LastAndLater

RESIDENT Data;

DROP Table Data;

RENAME Table NewData TO Data;

[effinty2112 · Accepted solution]

Hi Yaniv,

With your data you can obtain this straight table:

program	status	IS_LAST
10025	20	1
10025	30	0
10025	40	0
10025	60	0
10025	70	0
10079	70	1
10085	20	1
10085	30	0
10085	40	0
10085	60	0
10085	70	0

status is the calculated dimension:

=aggr(if(status >=Min(TOTAL <program>Aggr(if(IS_LAST=1,status),program,status)),status),program,status)

Regards

Andrew

[yanivvl0]

hi Andrew , great help - THANKS !!

[yanivvl0]

HI Peter , i need expression , see below, thanks.