# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld May 10-12, Online and Free! REGISTER NOW
cancel
Showing results for
Search instead for
Did you mean:
Partner

## Help with Set Analysis Expression

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.

1 Solution

Accepted Solutions
Partner
Author

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

7 Replies
MVP

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?

Partner
Author

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

Partner

Try this :

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

vDate = "=max(date)"

i hope this may helps you.

-Nagarjun

Partner
Author

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.

MVP

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

Partner
Author

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.

Partner
Author

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