topic Re: Sintax Problem with Date Range Set Analysis in QlikView

Sintax Problem with Date Range Set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Sintax Problem with Date Range Set Analysis

alexdataiq — Tue, 01 Nov 2016 17:26:28 GMT

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

Regards

Re: Sintax Problem with Date Range Set Analysis

Frank_Hartmann — Tue, 01 Nov 2016 17:36:18 GMT

try like this:

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

hope this helps

Re: Sintax Problem with Date Range Set Analysis

sunny_talwar — Tue, 01 Nov 2016 17:53:35 GMT

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)

Re: Sintax Problem with Date Range Set Analysis

johnw — Tue, 01 Nov 2016 23:32:56 GMT

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

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

Re: Sintax Problem with Date Range Set Analysis

Thu, 03 Nov 2016 13:04:40 GMT

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.

Re: Sintax Problem with Date Range Set Analysis

Frank_Hartmann — Thu, 03 Nov 2016 17:54:05 GMT

what about this expression:

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

Re: Sintax Problem with Date Range Set Analysis

johnw — Thu, 03 Nov 2016 19:09:16 GMT

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)

Re: Sintax Problem with Date Range Set Analysis

Fri, 04 Nov 2016 13:48:12 GMT

Perfect solution! Thank you!