Help with Set Analysis Expression

[hoangvvo]

Hi, How would i go about writing an expression for : The sum of total, where the DateField is equal to the max of DateField and where DateField is Less than or Equal to the SelectedDate Thanks Hoang.

[sunny_talwar]

Are these two separate conditions for two separate expressions? or needed in one expression? The first and second condition seems to be contradicting, isn't it? Can you share an example?

[hoangvvo]

inv:

load * inline

[id,total,date

1,10,2016-01-01 12:01:01

2,10,2016-01-01 12:01:01

3,10,2016-01-01 12:01:01

1,5,2016-03-01 15:01:01

3,15,2016-03-01 12:01:01

4,10,2016-04-01 12:01:01

1,15,2016-04-01 12:01:01

];

3 island table containing just 5 year, 12month and 31 day .

those are use for filter panel so i can select them:

If i  chose year : 2016, month, 3, day 31

I would have 2016-03-31 which i only want :

id,total,date

2,10,2016-01-01 12:01:01

1,5,2016-03-01 15:01:01

3,15,2016-03-01 12:01:01

as the result table.

the max date based on the selected panel when attempting to do this:

max(if(date<=MakeDate(year,month,day),date))

this gives me the max date exactly above, but how do i use this date to pull just the total out of the original table

[nagarjuna_kotha]

Try this :

Sum({<Date={"<=$(=vDate)"}>} Sales)

vDate = "=max(date)"

i hope this may helps you.

-Nagarjun

[hoangvvo]

That will not work because it will give me the total of max date. I just want the total depending on the date that is selected in the 3 island (unassociated table). The whole idea was that I would select a 'date' , which then would give me all entries prior or equal to the selected date, and then in that subset , i want to only display the max date, and all fields in that max date.

[sunny_talwar]

Do you plan to have always one selected value in your three Island Table?

[hoangvvo]

Yes, year is the default selected value of Year(now(2)) the rest can be selected if needing to go directly into those date range. if none is selected in month (12 is defaulted, and likewise 31 is the default) - This is not the most accurate but for now it will do as i m trying to figure out how to bring in the fields of a row ,based on the date testing first sorted value  based on date <= selected date.

[hoangvvo · Accepted solution]

I have this resolved using FirstSortValue firstsortedvalue({$<RowID={"=If($(MaxDate)=Date,RowID)"}>}[Total],-Date)

I added RowID for each row as the RowNo() during load and got the desire result

Thanks