Need help with Set Analysis

[#1]

Hello,

i hope somenone can help me with my Set Analysis Expression. I'm no native English speaker, i hope you apologize.

I have a relatively complex application model, so that i can't directly link the following dimension table to my facts table. That is, why i try to solve the expression using set analysis.

My dimension table looks like

SalesCampaign, CampaignStartDate, CampaignEndDate, ProductID

A, 2011-07-01, 2011-07-10, 1001

A, 2011-07-01, 2011-07-10, 1002

B, 2011-07-30, 2011-08-08, 1005

etc.

and the facts table contains - besides a dozen other columns - productId, date of sale, sold pieces, revenue

1001, 2011-06-30, 1, 10.50

1001, 2011-07-05, 2, 18.00

1004, 2011-07-05, 1, 45.00

etc.

What i try to accomplish is to sum up sold pieces and revenue per campaign product.

My Chart dimensions are SalesCampaign and ProductID and i tried to use Set Analysis to only get the relevant sales per product within the campaign period, but i can't get the expression working:

I tried out several "syntaxes" like

sum({$<%SalesDate= {">= date([CampaignStartDate]) <= date([CampaignEndDate])"}>}  revenue)

or

sum({$<%SalesDate= {"[%SalesDate] >= date([CampaignStartDate]) <= date([CampaignEndDate])"}>}  revenue)

or

sum({$<%SalesDate >= date([CampaignStartDate]),  %SalesDate <= date([CampaignEndDate])"}>}  revenue)

or even

sum({$<%SalesDate= {"[%SalesDate] >= date([CampaignStartDate])"}>}  revenue)

but neither seems to work.

I hope someone can give me a hint.

Thx in advance,

Markus

[chriscammers]

No Need to apologize for your English skills, you did very well.

My suggestion for you is to create an intermediate table between the campaign table and the fact table using IntervalMatch(). You will need to use the Help files to get the syntax for the extended properties that allow you to not only qualify the date range but also the foreign key value. from what you listed in your post I think it would look something like this...

CampaignLink:

IntervalMatch([date of sale], productid) Load CampaignStartDate, CampaignEndDate,  ProductID as productid Resident campaign;

Please treat this as pseudo-code, it will take some trial and error to get it working, you will most likely end up with a synthetic table for the CampaignStartDate and campaignEndDate but this is one of of the cases where I would consider leaving it in the model.

The trick here is that you cannot join the campaign link to your fact table because you will likely cause duplication in your fact table but by leaving it as a separate table you should be able to show totals for each campaign and not double count in the totals.

Good Luck

Chris

[#3]

Hi Chris,

thx for your reply.

I worked with IntervalMatch once, and usually try to avoid it whenever possible when working with large tables. From my earlier experiences i learned, that the reload times with intervalmatches gets really slow and often also cancels with errors, if the data table contains more than a million lines

And my facts table is really huge, consisting of several hundreds of millions of records.

I could try to solve the problem using IF expressions instead auf Set Analysis, but i heard, it should be less performant. And if you can imagine performance is really important within this application (with a file size of 3GB )

If it doesn't work (well) i'll try your recommendation.

[#4]

Ok, i solved it using

SUM(IF( %SalesDate >= date([CampaignStartDate]) AND %SalesDate <= date([CampaignEndDate], revenue, 0)

As long as we dont select the date from the facts table, this solution works - suprising performant too.

This solution is actually good enough for my application, allthough that's not my favorised one, but it seems as Qlikview doesnt support set analysis comparisons with other field values. Or i just cant find the right syntax