topic Re: Set analysis not working across time in QlikView

Set analysis not working across time

dadumas — Wed, 18 Jan 2017 13:07:21 GMT

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

Re: Set analysis not working across time

sunny_talwar — Wed, 18 Jan 2017 13:16:03 GMT

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

Re: Set analysis not working across time

dadumas — Wed, 18 Jan 2017 13:26:50 GMT

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.

Re: Set analysis not working across time

dadumas — Wed, 18 Jan 2017 17:06:24 GMT

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] )

Re: Set analysis not working across time

swuehl — Wed, 18 Jan 2017 17:37:59 GMT

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

Re: Set analysis not working across time

dadumas — Wed, 18 Jan 2017 22:05:12 GMT

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

Re: Set analysis not working across time

swuehl — Wed, 18 Jan 2017 22:33:43 GMT

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

Re: Set analysis not working across time

dadumas — Thu, 19 Jan 2017 01:31:46 GMT

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

Re: Set analysis not working across time

sunny_talwar — Thu, 19 Jan 2017 02:46:58 GMT

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.

Re: Set analysis not working across time

dadumas — Thu, 19 Jan 2017 12:42:45 GMT

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

Re: Set analysis not working across time

dadumas — Thu, 19 Jan 2017 12:44:17 GMT

Correction to the just previous reply:

The Entered Date would have to be less than or equal than the max(DateKey) for the Month.

Dave

Re: Set analysis not working across time

dadumas — Thu, 19 Jan 2017 14:28:27 GMT

Sunny,

To more accurately reflect the data model, I added the MasterCalendarLink table, and added more dates to the calendar. Adding a DateType broke your expression, but DateType is necessary in the data model. Your expression still calculates, but is not correct. I put notes, and added another chart for clarity on why DateType was added.

Dave

Re: Set analysis not working across time

sunny_talwar — Thu, 19 Jan 2017 15:09:36 GMT

A solution with IntervalMatch

Script changes

and also

Fct_Main:

Load

CaseNumber,

Date(EnteredDate) as EnteredDate,

Date(ResolvedDate) as ResolvedDate,

Date(AddMonths(ResolvedDateNotNull, -1)) as ResolvedDateNotNull,

AutoNumberHash128(EnteredDate,ResolvedDateNotNull) as MasterCalendarLink

;

Load * inline [

CaseNumber,EnteredDate,ResolvedDate,ResolvedDateNotNull

1,1/1/2016,1/1/2016,1/1/2016

2,2/1/2016,3/1/2016,3/1/2016

3,3/1/2016,4/1/2016,4/1/2016

4,2/1/2016,,1/1/3000

5,1/1/2016,3/1/2016,3/1/2016

];

and

//NoConcatenate

//MasterCalendarLink:

//Load DISTINCT

//AutoNumberHash128(EnteredDate,ResolvedDateNotNull) as MasterCalendarLink,

//EnteredDate as DateKey,

//'Entered' as DateType

//Resident Fct_Main

//;

//Concatenate (MasterCalendarLink)

//Load DISTINCT

//AutoNumberHash128(EnteredDate,ResolvedDateNotNull) as MasterCalendarLink,

//ResolvedDateNotNull as DateKey,

//'Resolved' as DateType

//Resident Fct_Main

//;

MasterCalendarLink:

IntervalMatch(DateKey)

LOAD EnteredDate,

ResolvedDateNotNull

Resident Fct_Main;

Re: Set analysis not working across time

swuehl — Thu, 19 Jan 2017 15:35:58 GMT

I personally would prefer the while loop here, if you need to create a reference date per date in your interval.

The result would be the same, but I think a while loop could perform better (you don't need to match against any master calendar dates that are outside the interval) and you can also easily create flags for e.g. closing and entry date in the link table itself (thus allowing also counting only new / closed tickets in the same chart using Reference date as dimension).

Re: Set analysis not working across time

swuehl — Thu, 19 Jan 2017 15:39:56 GMT

David, why are you trying to avoid the date link table with the reference dates?

Sure, it will create another table, but even with some thousand cases lasting some years, it will still be only some million records.

You could use a date island to use as dimension and use advanced aggregation and a condition to check against your interval, but I think this will essentially just blow up the chart memory instead of your data model. And run time performance will be worse.

Re: Set analysis not working across time

dadumas — Thu, 19 Jan 2017 19:07:01 GMT

Yes, I noticed that Sunny's solution did not factor in the DateType and my MasterCalendarLink table, as you suggest in your comment:

closing and entry date in the link table itself (thus allowing also counting only new / closed tickets in the same chart using Reference date as dimension).

However, I am amazed at how fast he was able to meet the requirement I needed! I also learned a bit about intervalmatch. If intervalMatch can work, somehow, it allows for a much leaner data model.

The issue with the while loop is that, there are millions of cases, and if the average case closes in 3 days for example, that adds a 3X factor on those rows in the bridge table.

I may just rethink this, and calculate a "Case age in days", and then report by MonthYear with <DateType={'Entered'}>. I think Users will get the picture. I can still show CaseCount along with that.

Dave

Re: Set analysis not working across time

dadumas — Thu, 19 Jan 2017 19:10:04 GMT

Sunny,

I just added a reply to Stefan. Please see that. I have not given up on IntervalMatch, however. I just need it to also take in consideration multiple date types (Entered, Resolved, Responded,etc) to be able to accomodate other charts where I sum case counts by those. I am very impressed by how fast you have created these solutions!

Dave

Re: Set analysis not working across time

sunny_talwar — Thu, 19 Jan 2017 19:23:46 GMT

Creation of flags using While Loop or IntervalMatch is going to require a lot of resources.... I have done that within the sample as it was simple to do it from less records, but you can take a look

Was not sure how to check for Responded as there was not Responded date I think.....

Re: Set analysis not working across time

dadumas — Fri, 20 Jan 2017 13:15:36 GMT

The Date link table I use when I have multiple date "roles" within the same fact. I do need that, as in this example, I have roles of Resolved and Entered, and need separate measure for that under a single master calendar. I am trying to avoid an additional CaseOpenIntersection table which would have a row for each MonthYear (period) that case is open. That is what would add millions of rows. I am hoping to avoid that by using CaseAge accoss period entered.

Re: Set analysis not working across time

dadumas — Wed, 25 Jan 2017 19:58:06 GMT

Sunny

Sorry for the late response. I looked at your intervalmatch table, and it looks like it might parallel the separate table that I think might be best to implement if I need it. That table would just loop tickets, and create a separate row for each period that a ticket is still open (between entered date and resolved date). I am hoping my idea to use a [Ticket Age] metric, and just show # tickets by age over time (based on Entered Date), will suffice. If it does, I do not need to build a separate "Ticket Period Open" table.

Thanks so much for your help on this.

Dave