Relatively New to QlikView / Have a Set Analysis Problem

[Anonymous]

Hi,

I have a need to determine the average cost of warranty service in the first 30, 60 and 90 days after systems have been installed, grouped by the fiscal quarter in which they were installed.  I'd like to include all systems installed in a given quarter in my cost average and not just those that actually had cost.

I have two simple tables with many dimensions.  There are many other dimensions that I don't list here, all of which may be used for selections.  But the dimensions and measures for my final object are defined below;

Financials table:

The equipment receiving the costs is defined by a field named [F/L#].

Warranty costs are in a field named [Cost].

The date of the cost posting is in a field named [Cost Date].

Equipment table:

Joined to the Financials table by the [F/L#] field.

The date of installation is in a field named [Startup Date]

The fiscal quarter is in a field named [Startup FY/Quarter]

The Equipment table has a 1 to many relationship to the Financials table.

My object will be a pivot table in which the [Startup FY/Quarter] will be the only row dimension.  My columns will be comprised of a calculation that depicts the average warranty cost in 0-30 Days, 0-60 Days and 0-90 Days.  Again, I need to average the cost against all systems and not just those with cost.

I'm not a SQL guy, but here is my feeble attempt to depict what I'm trying to do.

Sum([Cost])/Count(Distinct [F/L#]) as [0-30 Days],

Where [Cost Date] Between [Startup Date] and [Startup Date]+30

Group By [Startup FY/Quarter]

I would repeat this expression for the 0-60 Days and 0-90 Days calculations.

I hope I've provided a clear enough explanation and perhaps this brain-teaser will be helpful to others.  Thank you in advance for any light you can shed.

[jonathandienst]

This sort of problem is best solved by adding some derived fields to assist in the calculations. So I would load the data like this:

Equipment:

LOAD ....

From Equipment;

mapStart:

Mapping LOAD [F/L#],

  [Startup Date]

Resident Equipment;

Financials:

LOAD *,

  If(ElapsedDays <= 90, 1, 0) As [0-90],

  If(ElapsedDays <= 60, 1, 0) As [0-60],

  If(ElapsedDays <= 30, 1, 0) As [0-30],

;

LOAD [F/L#],

  Cost,

  [Cost Date],

  [Cost Date] - ApplyMap('mapStart', [F/L#], 0) As ElapsedDays

  ...

From Financials;

Now you can use the aging bucket flags in your expressions:

=Sum({<[0-30] = {1}>} Cost)

=Sum({<[0-60] = {1}>} Cost)

=Sum({<[0-90] = {1}>} Cost)

[jonathandienst]

I used bucket flag fields rather than an age bucket field because the elapsed day ranges overlap. If you used normal 30 day buckets, then the preceding load would be:

LOAD *,

  If(ElapsedDays <= 30, '[0-30]',

  If(ElapsedDays <= 60, '[31-60]',

  If(ElapsedDays <= 90, '[61-90]'))) As Age

;

And the expressions would be of the form:

=Sum({<Age = {'[0-30]'}>} Cost)....

[Anonymous]

Hi Jonathan,

Thank you so much for your reply.  After studying it, I understand what it's trying to do and it makes perfect sense.  Inserting the mapping load after the Equipment table script seemed to work just fine.  My challenge is that a complex creation of the Financials table makes it a bit confusing as to where to insert the preceding load script.  That table is built with 5 load statements that bring in QVDs from 5 fiscal years, appending them all together.  It is then followed by 4 left join loads that bring in related dimensions from other QVDs.  Once this is all done, I do end up with a single Financials table.

Here's how the load statement starts, bringing in financial data from FY11.  This is repeated for FY12 through FY15.  The [F/L#] field doesn't enter until I do a left join on the [Notification] field from another QVD.  Where in all this madness should the preceding load script be inserted?  The [F/L] field doesn't come in until about line 145 of a 167 line load script.