Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Intersecting Values with Set Analysis

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. 

Capture.JPG

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.

Capture2.JPG

 

 

 

 

 

 

 

Thank you in advance!

Regards,

Richard

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

Merknad 2019-07-22 120141.png

View solution in original post

7 Replies
Highlighted
MVP
MVP

[ 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:

Merknad 2019-07-21 211929.png

Merknad 2019-07-21 211034.png

 

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

Merknad 2019-07-21 210946.png

 

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;

Highlighted
Partner
Partner

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!

Highlighted
MVP
MVP

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

Merknad 2019-07-22 120141.png

View solution in original post

Highlighted
Partner
Partner

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..

Highlighted
MVP
MVP

[ 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.

Highlighted
Partner
Partner

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

Highlighted
MVP
MVP

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