topic Re: Set analysis modifier between two dates in QlikView

Set analysis modifier between two dates

Wed, 25 Nov 2020 16:16:04 GMT

Hello folks,

Please help me. I'm going mad.

I'm trying to do something using set analysis that I think ought to be simple, but I just can't seem to get the syntax right.

In the attached file I have two tables: calendar and event. They are linked through the date field.

With Date as the dimension, I want to plot "Result" from the Event table as a dot, and "Open" from the Calendar table as a circle. So that every available day is shown on the chart, with a dot for every event that occurred.

I want to be able to select a month from Calendar and an Event type from events and see a circle for every day that month and a dot for every event.

I think I need something like

=avg( {1 <Date={"<max(Date)>min(Date)"} } Open)

but I can't get it right. There needs to be a dollar or two in there and I can't figure out where!

I even messed with the P() function, but ended up confusing myself.

Any help greatly appreciated!

Karen

Re: Set analysis modifier between two dates

sunny_talwar — Thu, 01 Jun 2017 11:47:49 GMT

May be this

=Avg({1<Date = p(Date)>}Open)

Re: Set analysis modifier between two dates

Thu, 01 Jun 2017 11:56:49 GMT

Thank you Sunny.

That's not quite what I need. It plots only the Dates in the current selection. I want ALL the Dates, within the max and min Dates of the current selection.

Does that make sense?

Re: Set analysis modifier between two dates

sunny_talwar — Thu, 01 Jun 2017 12:00:25 GMT

May be this

=Avg({1<Date = {"$(='>=' & Min(Date) & '<=' & Max(Date))"}>}Open)

Re: Set analysis modifier between two dates

Thu, 01 Jun 2017 12:03:54 GMT

Yes!!!! Thank you so much! I lost a whole day trying to do that!