Rolling 52 weeks - need to use SA because of the data model

[blaise]

Hello

Im facing the same problem as quite a few fellow developers, i need to create a rolling 52 week calculations with a total of three dimensions;

# of Breakdown

# of instruments under warranty

# Breakdown / instrument under warranty

The result should be displayed in a

I have looked through the nice post by Gysbert Wassenaar, http://community.qlik.com/docs/DOC-4252, concering the different techniques around this. However, my datamodel is not as easy as the examples.

The breakdowns are found in my fact table with an attached calendar on breakdown date. There is also a instrument table attached as a dimensional table, a breakdown is a fault on one instrument. With this data i can use almost all techniques to create the rolling 52 week calculation, but i can't apply a calculations to only count instruments under warranty.

If I select a single period i can determine the # of instruments under warranty with a set analysis (that checks if the selected period lies within the warranty period (warranty start and warranty end). I do this by with a

count(distinct total {1<instrument_valid_from ={"<=$(=min(date))"}, instrument_valid_to ={">=$(=min(date))"}   >} instrument)

calculcation (havn't found a way to do it by not using {1}.

I have, for another application where i do some other calculations for "instruments under warranty" created an intervalmatch join between date and instrument. But with the amount of instruments (~50.000) this is gonna big quite a big table in a year or two.

Can anyone of you think of a solution to this? As the old report the customer has used in an excel sheet without any selection or drill down possibilities one solution might be to precalculate the values in the script but i would like to keep the selection and drill down possibilities if it is possible.

I've attached a sample application so you have something to play around with