Sintax Problem with Date Range Set Analysis

[#1]

I'm trying to get all the values after 01/03/2016 and before the date in column "date_end" ,something like that: 

=Avg({<Date = {">=01/03/2016<=Data_end"}>} value)

but can't get right the sintax, can someone help me?

Thanks,

Vinicius.

[alexdataiq]

Can you provide a little sample application? Are you using this as an expression in a chart, variable, textbox?

Regards

[Frank_Hartmann]

try like this:

Sum({<Date={'>=$(=MonthStart(makedate(2016,3))) <=$(=Date(max(date_end)))'}>} value)

hope this helps

[sunny_talwar]

Or this if date doesn't default to DD/MM/YYYY (if 01/03/2016 means March 1st 2016)

Avg({<Date={">=$(=Date(MakeDate(2016, 3, 1), 'DD/MM/YYYY'))<=$(=Date(Max(date_end), 'DD/MM/YYYY'))"}>} value)

or this if the date is MM/DD/YYYY

Avg({<Date={">=$(=Date(MakeDate(2016, 1, 3), 'MM/DD/YYYY'))<=$(=Date(Max(date_end), 'MM/DD/YYYY'))"}>} value)

[johnw]

Throwing out yet another option. Might have errors of my own, though.

avg({<Date={"=Date>=makedate(2016,3) and Date<=max(date_end)"}>} value)

[#6]

None of the solutions worked, all of them got the lower limit right, no data before march, but the upper limit was not right, maybe with a sample application someone can solve the problem.

load * Inline [ 

Number, value, Date, Date_end

1, 4, 01/02/2016, 07/06/2016

1, 3, 04/04/2016, 07/06/2016

1, 7, 25/05/2016. 07/06/2016

1, 30, 30/09/2016, 07/06/2016

1, 15, 17/10/2016, 07/06/2016

2, 17, 10/02/2016, 25/09/2016

2, 45, 30/04/2016, 25/09/2016

2, 4, 14/07/2016, 25/09/2016

2,7, 20/10/2016, 25/09/2016

];

Every number have the same Date_end, so I should get the average 5 for number 1 and average 24.5 for number 2.

I'm trying to do it as an expression of a pivot table.

[Frank_Hartmann]

what about this expression:

=avg({<Date={"=Date>=makedate(2016,3) and Date<=Date(Max(Date#(Date_end,'DD/MM/YYYY')))"}>} value)

[johnw · Accepted solution]

Ah. Set analysis doesn't work in the context of dimensions.

avg({<Date={"=Date>=makedate(2016,3)"}>} if(Date<=Date_end,value))

But if that's your requirement, I'd just add a flag to your data model. It's more efficient. Name it something appropriate.

,if(Date>=makedate(2016,3) and Date<=Date_end,1) as Flag

avg({<Flag={1}>} value)

[#9]

Perfect solution! Thank you!