Set analysis not working across time

[dadumas]

I have an application that measures open and resolved help ticket incidents (cases).

I have a MasterCalendar dimension with many fields, 2 of which are:  Date and MonthYear.  Ex, 01/17/2017 and Jan2017

I have an expression below, which calculates correctly when a MonthYear is selected, but does not work (greatly reduces the sum calculated), when not selected and data is summed across MonthYear in chart showing the measure across time.  All chart types show the same result, so think of a straight table here.

The columns [Entered Status Date] and %ResolvedDateNotNull, are dates, always populated, and are stored in the fact table.  [Case Count] is stored as a 1, and is also in the fact,  Facts are linked to the MasterCalendar through a MasterCalendarLink table, on a field named %MasterCalendarLink.  MasterCalendarLink has the %EntryDateFlag.  That linkage works fine, and I have used this date design in many applications in which there are multiple date "roles" across the same facts.  In this case, %EntryDateFlag is the "role" used for [Entered Status Date]

What this below does it to show open incidents (not resolved during the MonthYear in the chart) across time. 

sum({$<%EntryDateFlag={1}, [Entered Status Date]={"<=$(=max(Date))"}, %ResolvedDateNotNull={">$(=max(Date))"}>} [Case Count] )

Dave

[sunny_talwar]

Set analysis is evaluated once per chart not per dimension. May be you need to use Aggr() instead of set analysis here? May be a sample might be able to help us understand what you want and will aid in faster help

[dadumas]

Not sure what you mean by evaluated "once per chart". Set Analysis is evaluated for all rows that it's measure applies to.  I will get a sample together, as you suggest.  thanks.

[dadumas]

I figured this out.  Since this is a comparision of 2 fields, the p() possible needs to be used for the compare.  Works great now.

sum({$<[Entered Status Date]={"<=$(=p(Date))"}, %ResolvedDateNotNull={">$(=p(Date))"}>} [Case Count] )

[swuehl · Accepted solution]

I doubt that you can use the p() function within the dollar sign expansion. I assume both expansions will just be empty.

Also you are not comparing field values on a per record base using above syntax, if that's what you have intended, e.g.

{< [Entered Status Date]={SomeValues} >} is just assigning SomeValues as selections to field [Entered Status Date], in the context of the aggregation it is used in.

But to prove that I am wrong, you can set up a sample QVW...

What Sunny was trying to say above is, that Set Analysis as well as Dollar sign expansions will be evaluated only once per chart, i.e. that the chart dimensions can't influence the record set used in the aggregation (i.e. like you are applying a manual selection of field values for the aggregation, but you can't do this per dimension line).

So if your dimension is a time dimension and you want your aggregation use a different record set per dimensional value, set analysis alone is not the way to go.

Evaluating "sets" in the context of a dimension

You can look into creating a reference date table, like shown in

Creating Reference Dates for Intervals

There are also tons of examples for open issue / ticket count here in the forum, which you may use as starting point for your case.

Regards,

Stefan

[dadumas]

Stefan,

Thanks for weighing in, and the links you provided,  Dollar Sign Expansions can absolutely be used inside ""  search strings within set analysis.  I have created hundreds of set analysis expressions this way, across many projects. In the book QlikView 11 for Developers [aka "the Bible" ], you will see many Dollar Sign Expansions used in search strings within set analysis, invaluable for period date reporting.  I have never used them with p(), however, so yes, new territory here on that.

Regarding :

{< [Entered Status Date]={SomeValues} >} is just assigning SomeValues as selections to field [Entered Status Date], in the context of the aggregation it is used in.

That sounds like exactly what I am trying to accomplish.  I.E. The Date dimension will sum [Case Count], based on Entered Status Date] across MonthYear in the chart.

When I have some time, I will put together a sample, but so  far, it seems to be working.  Oops - that sounds dangerous

Dave

[swuehl]

I would be interested to see this sample

But no worries, good to hear that it works for you (though my assumption is that it could probably work also without the set expression then).

Just to clarify:

I haven't doubted that dollar sign expansion in general is not working, but that the p() function can't be used that way.

Regards,

Stefan

[dadumas]

Stefan,

Indeed, as you say, this does not work.  p() cannot be used in this way.  I have attached a sample, proving this with a small control sample data set.

So,

I am thinking I need an intersection table, which, via a loop (from your link example) , which adds MonthYear rows for which a Case is still open, up until it is resolved.  I can then sum up those rows by MonthYear.

It bothers me that I would have to do this, as it will bloat the application data model.

Thanks for your wisdom on this one.

Dave

[sunny_talwar]

You can either use a IntervalMatch() in the script to do this

IntervalMatch

or you can use a front end Aggr() function (which I did mention to you in my earlier response also ). Not entirely sure what the expected output is, but see if this is what you want in your sample.

Expression used:

=Sum(Aggr(If(EnteredDate <= DateKey and ResolvedDate > DateKey, CaseCount), CaseNumber, DateKey))

Probably not a great idea to do this and would recommend looking into IntervalMatch to connect your calendar to the date range of Entered and Resolved date.

[dadumas]

Thanks Sunny!  The ResolvedDate would have to be greater than the max(DateKey) for the Month, and the  Entered Date would have to be less than or equal than the min(DateKey) for the Month.  I should have built the calendar in my example with all the dates within each month.  The goal is to show a historical account of open cases period by period. 

I will load a better calendar and then try to tweak your sum(aggr(if))) statement, but I agree as you say that would not be great for performance.  I have not used IntervalMatch - I thought it was just there to build slowly changing dimensions, which I do in my ETL processing.  Should have listened to you and looked at that.  I will check out you ink and read up on it.  Thanks again for getting back.  I will post a better example later...  I am really trying to avoid creating an intersection table as I mentioned in my previous reply, so this is all good stuff.

Dave