Calculation triangle with Set analysis

[mironovd]

Hi

I have two tables:

#	date	product	premium	Max_Interval
1	Jan-18	A	5	3
2	Feb-18	A	10	3
3	Mar-18	A	15	3
4	Apr-18	A	11	3
5	May-18	A	12	3

№	product	ratio
1	A	0,50
2	A	0,60
3	A	0,90

need a expression in straight table to show this result:

date	product	result
Jan-18	A	4,50
Feb-18	A	12,00
Mar-18	A	22,00
Apr-18	A	23,90

comment:

4,50	#1. (№ = Max_Interval =3 --> ratio =0,90 )*( premium = 5)
12,00	#2. (№ = Max_Interval =3  --> ratio =0,90)*( premium = 10) +  #1. (№ = 2  --> ratio =0,60)*( premium = 5)
22,00	#3. (№ = Max_Interval =3 --> ratio =0,90)*( premium = 15) +  #2. (№ = 2 --> ratio =0,60)*( premium = 10) + #1. (№ = 1 --> ratio =0,50)*( premium = 5)
23,90	#4. (№ = Max_Interval =3 --> ratio =0,90)*( premium = 11) +  #2. (№ = 2 --> ratio =0,60)*( premium = 15) + #1. (№ = 1  --> ratio =0,50)*( premium = 10)

how to do this ? thanks

[chrismarlow]

Hi,

You can use Above to return values associated with dimension set for the row above, so changing your date for ease of sorting, returns values in the top grid that show the break out of the values.

For adding them back together for January & February to show you have to handle the nulls, or you get null over all (bottom left), you do that with IsNull (bottom right),  so who formula would be;

sum(premium)*0.9+if(IsNull(Above(sum(premium),1,1)*0.6),0,Above(sum(premium),1,1)*0.6)+if(IsNull(Above(sum(premium),2,1)*0.5),0,Above(sum(premium),2,1)*0.5)

This might need {<Max_Interval={'3'}>} in all the sums ...

Cheers,

Chris.

[mironovd]

Hi, Chris

Thanks for your reply.

But it will not be a solution for other products

I have 40 products and each of them has its own group of ratios.

The number of ratios in each group will be different.

The formula must be dynamic and without constants

[chrismarlow]

Hi,

If you have a set maximum number of ratios then there is an extension to what I suggested that might work, but you are going to end up with long formulae & it may be better to try to do something in script to link together.

It does rely on an association in the script, so my toy project has;

data:
LOAD * INLINE [
number,date,product,premium,Max_Interval
1,01/01/2018,A,5,3
2,01/02/2018,A,10,3
3,01/03/2018,A,15,3
4,01/04/2018,A,11,3
5,01/05/2018,A,12,3
1,01/01/2018,B,5,3
2,01/02/2018,B,10,3
3,01/03/2018,B,15,3
4,01/04/2018,B,11,3
5,01/05/2018,B,12,3
];

ratio:
LOAD * INLINE [
rationumber,product,ratio
1,A,0.5
2,A,0.6
3,A,0.9
1,B,0.75
2,B,1
];

Where I have just made up product B as a copy of A & set it to have only 2 ratio entries, so I can then use FirstSortedValue to pick the ratio & you can see for A I get the same values as before, for B there are no values in 3rd column and 3.75=5*0.75 etc;

You can wrap alt around this instead of using If(IsNull, but is still going to be quite long, especially  if you have many ratio for certain products.

sum(premium)*firstsortedvalue(ratio,-rationumber,1)+alt(Above(sum(premium),1,1)*firstsortedvalue(ratio,-rationumber,2),0)+alt(Above(sum(premium),2,1)*firstsortedvalue(ratio,-rationumber,3),0)

Cheers,

Chris.

[mironovd]

Chris,

Thank you very much! 

This is an interesting solution but has a limitation:

it is necessary to fix rationumber in the formula.

In this example, the maximum number is 3, but in the data model it is equal to 20 and 60.

I tried :

1.  Concatenate tables ( add columns:  premium/ratio , serial number)

use the formula: 

aggr(rangeSum(Above(sum({<...={"premium"}>} value)),0,serial number)*Below(sum({<...={"ratio"}>}  value),0,serial number)),number)

- this does not work

2. combine ratio 

№	product	ratio_2
1	A	"0,9"
2	A	"0,9","0,6"
3	A	"0,9","0,6","0,5"

extract :

= {"=$(SubField(FieldValue(...."}

it didn't work either  😞

[chrismarlow]

Hi,

I agree if a product premium can have 60 ratios then this approach will fail. I think it may also fail to pick up if you have dates missing.

So I think you have to script. If I add the below to the bottom of the toy script before;

left join (ratio)
load
 product,
 max(rationumber) as max_rationumber
resident ratio
group by product;

left join (ratio)
load distinct
 product,
 date
resident data;

ratio_final:
NoConcatenate
Load
 product,
 ratio,
 date as ratio_date,
 AddMonths(date,rationumber-max_rationumber) AS date
Resident ratio;

drop table ratio;

This creates a many to one relationship between ratio_date (and associated ratio) and date, that can then be used in the calculation;

So filtering on ratio_date=01/03/2018 the grid on the right shows the constituents with the top 3 being the calculation from your first post for that date, note in the left the use of ratio_date to aggregate, but the simplicity of the formula.

I will leave you to add 20 to 60 rows of data and test it.

Cheers,

Chris.

[mironovd]

Hi Chris !

Result   =  0  (+ Synthetic key):

script:

data:
LOAD * INLINE [
number,date,product,premium,Max_Interval
1,01/01/2018,A,5,3
2,01/02/2018,A,10,3
3,01/03/2018,A,15,3
4,01/04/2018,A,11,3
5,01/05/2018,A,12,3
1,01/01/2018,B,5,3
2,01/02/2018,B,10,3
3,01/03/2018,B,15,3
4,01/04/2018,B,11,3
5,01/05/2018,B,12,3
];
ratio:
LOAD * INLINE [
rationumber,product,ratio
1,A,0.5
2,A,0.6
3,A,0.9
1,B,0.75
2,B,1
];

left join (ratio)
load
product,
max(rationumber) as max_rationumber
resident ratio
group by product;
left join (ratio)
load distinct
product,
date
resident data;
ratio_final:
NoConcatenate
Load
product,
ratio,
date as ratio_date,
AddMonths(date,rationumber-max_rationumber) AS date
Resident ratio;
drop table ratio;

[chrismarlow]

Hi,

On the synthetic key - Should We Stop Worrying and Love the Synthetic Key - but you can get rid of it.

Grabbing the code you pasted & re-running works for me … do you see this SET DateFormat='DD/MM/YYYY'; around row 7, or something else?

Trying to attach the QVW, but keep getting an error.

Cheers,

Chris.

[mironovd]

Hi

Chris, thanks for your help!

The problem was in the format   'DD.MM.YYYY'