Intersecting Values with Set Analysis

[glencote]

Hi All,

I have attached a Test.qvw and Test.xlsx to make this request a bit easier hopefully. 

I need to, using a pivoted spreadsheet find the intersecting values between 2 calculated expressions (for convenience i have simplified these in the example) - Turnover and Margin%. If the Turnover is between the threshold provided and the intersecting Margin% falls in one of the Margin Tiers the intersecting value must be provided as the result. 

I am not considering Intervalmatch because the Turnover and Margin% are actually complex calculations calculated in the frontend and there is no link to this spreadsheet using store or any other field. It is a global guideline.

Thank you in advance!

Regards,

Richard

[petter]

[ TIP: to show any contributor that you like and appreciate their answers please CLICK the like icon (thumbs up) ]

I had a look at your problem and came up with an approach that use IntervalMatch twice with the first one is using the straight forward IntervalMatch while the second one is using the extended IntervalMatch.

Mind you that the test data I had to modify due to most of the test cases were edge cases that didn't come up with anything else than 0 as WeigthedInterestRate. Furthermore I haven't included the edge intervals between 1 and the first  turnover tiers. So you have to add a little bit of extra effort to include them...

Here is the interesting part of the load script:

Here is the first part of the load script - the Sales data used that I modified:

Here is the load script:

MFCVIB:
CrossTable(TurnoverTier, WeightedInterestRate, 3)
LOAD F1 as MarginTier,
F2 as MarginTierLower,
F4 as MarginTierHigher,
[5830000],
[6560000],
[6870000],
[7190000],
[7500000],
[7810000],
[8130000],
[8440000],
[8750000],
[9070000],
[9380000],
[9690000],
[10010000],
[10320000],
[10630000],
[10950000],
[11260000],
[11570000],
[11890000],
[12200000],
[12510000]
FROM [Test.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Test]);

Sales:
LOAD RecNo() AS Store#,* INLINE [
Store, Turnover, Margin%
one, 6644883, 4.54
two, 8000234, 3.57
three, 12466678, 6.24
four, 9540087, 2.47
five, 10433357, 3.02
six, 6254478, 2.72
seven, 11143256, 2.91
eight, 10117489, 2.94
nine, 9945722, 3.14
ten, 7115789, 2.9
];

M:
LOAD
MarginTier,
MarginTierLower,
MarginTierHigher,
//If(Mod(RecNo()-1,12)=0,1,Peek('TurnoverHigher')+1) AS TurnoverLower,
If(RecNo()=1,1,Peek('TurnoverHigher')+1) AS TurnoverLower,
Num#(TurnoverTier) AS TurnoverHigher,
WeightedInterestRate
RESIDENT
MFCVIB;

INNER JOIN IntervalMatch(Margin%) LOAD MarginTierLower,MarginTierHigher RESIDENT M;
JOIN (M) LOAD * RESIDENT Sales;
INNER JOIN IntervalMatch(Turnover,Margin%) LOAD TurnoverLower, TurnoverHigher, Margin% RESIDENT M;

DATA:
NOCONCATENATE LOAD DISTINCT Store#,Store,Turnover,Margin%,WeightedInterestRate RESIDENT M;

DROP TABLE MFCVIB, M, Sales;

[glencote]

Hi Petter,

Thank you so much for the reply and the effort put in, i really do appreciate it. I am well aware of interval match and use it often.. However with this particular problem my Margin% is actually a very complicated formula in the front end that cannot be replicated in the script and therefor cannot be linked to the spreadsheet that way. Is there no way this could be done in the front end without interval match?

Thanks again!

[petter · Accepted solution]

Sure - this can be done in the front-end or in a chart too:

[glencote]

Hi Petter,

Thanks again for the reply. I think your expression in the chart you showed is after the Interval Match was used to link the intersections.. I think we are missing each other slightly here.

I simplified the example i attached- in my main application (which i cannot disclose) the Margin% is a very complex formula which calculates values from different tables and uses 3 variables which the client changes on the front end - so to have this as a linking field in Interval match is near impossible. 

I don't think this is possible without interval match because you have to cater for every scenario which creates the Weighted Interest rate, it would be a nested if statement hundreds of lines long without the Interval Link (i assume).

Thank you again for your help..

[petter]

[ NOTE: to show any contributor that you like and appreciate their answers please CLICK the like icon (thumbs up) ]

No I haven't made the chart dependent on any IntervalMatch. But I have made the TurnoverTier into a Lower and Higher just like the MarginTier... that doesn't require any IntervalMatch as it is quite simple.

[glencote]

Apologies Petter, this was an error on my side. Looks like it is working!

[petter]

[ TIP : to show any contributor that you like and appreciate their answers please CLICK the like icon (thumbs up) ]