Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
Can you provide a little sample application? Are you using this as an expression in a chart, variable, textbox?
Regards
try like this:
Sum({<Date={'>=$(=MonthStart(makedate(2016,3))) <=$(=Date(max(date_end)))'}>} value)
hope this helps
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)
Throwing out yet another option. Might have errors of my own, though.
avg({<Date={"=Date>=makedate(2016,3) and Date<=max(date_end)"}>} value)
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.
what about this expression:
=avg({<Date={"=Date>=makedate(2016,3) and Date<=Date(Max(Date#(Date_end,'DD/MM/YYYY')))"}>} value)
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)
Perfect solution! Thank you!