how to summary in set analysis by dimension

[#1]

I have 2 table

first table

Parent	Child	Code	Budget
A	AA	1	100
A	AAA	1	200
A	AAAA	1	300
B	BB	1	300
C	CC	2	50
C	CCC	2	1500

second table

Code	From	To	Discount
1	1	350	10%
1	351	999999	30%
2	1	30	5%
2	31	1600	20%
2	1601	999999	40%

Those 2 table already link by code. I want to summary budgets and find the discount within range from and to in a new pivot chart.

Pivot chart

Parent	Child	Budget	Discount Cal (Correct)	Discount Cal (by my expression)
A	AA	100	30%	30%
	AAA	200	30%	30%
	AAAA	300	30%	30%
	Total Child	600	30%	30%
B	BB	300	10%	30%
	Total Child	300	10%	30%
C	CC	50	20%	40%
	CCC	1500	20%	40%
	Total Child	1550	20%	40%
Total Parent		2450	-	-

the expression that I use in 'discount cal' column

Only({$<From={"<=$(=Sum(Budget))"},To={">=$(=Sum(Budget))"}>} Discount)

My problem is the 'discount cal' that i get is max discount. Somebody help me to solve my problem.

thank you.

[hic]

You should fix this in the script using intervalmatch. For example:

Hierarchy:
Load * From Table1 (…) ;

Discounts:
Load * From Table2 (…) ;

Left Join (Hierarchy)
IntervalMatch (Budget, Code)
Load From, To, Code Resident Discounts;

Left Join (Hierarchy)
Load * Resident Discounts;

Drop Table Discounts;

But what I don't understand is how you can say that AA should have 30% discount. A budget of 100 implies 10%, not 30%.

HIC

[Digvijay_Singh]

See if this is correct, followed the advice from Henric

Your correct column is not reflecting right percentage it seems, please verify -

[#4]

Thank you 'Digvijay Singh' and 'Henric Cronstrom' for the answer but it's not correct for this because the budget as calculate for discount is summary total budget group by parent for example AA,AAA,AAAA the budget for cal discount is 600 so that mean this group have discount is 600.

Why I not use interval match because the budget had controlled by month and year that mean the discount not fixable if i change to see another year. Could you have another way?

Thank you

[Digvijay_Singh]

We can apply interval match to sub of budget group By Parent. But need some extra script.

Check this out -

[#6]

Thank you Digvijay Singh, In my opinion this way is mean it be fix summary budget but my budget is control by month and year. Sorry, again I think my table make you something wrong because that table is my pivot table but the raw budget table like this.

Parant	Child	Budget	Code	Date	Month	Year
A	AA	50	1	01/01/2014	Jan	2014
A	AA	30	1	01/01/2015	Jan	2015
A	AAA	100	1	13/01/2015	Jan	2015
A	AAA	100	1	01/03/2015	Mar	2015
A	AAAA	150	1	02/03/2015	Mar	2015
A	AAAA	150	1	10/03/2015	Mar	2015
B	BB	100	1	01/01/2014	Jan	2014
B	BB	50	1	01/02/2014	Feb	2014
B	BB	150	1	01/03/2015	Mar	2015
C	CC	50	2	01/01/2014	Jan	2014
C	CCC	500	2	01/02/2014	Feb	2014
C	CCC	500	2	01/02/2015	Feb	2015
C	CCC	500	2	01/03/2015	Mar	2015

That mean the summary of budget should be dynamic with month and year selection. This is my reason to find solution for expression not in load script(If I use load script it's mean size of file is very very big).

Thank you.

[hic]

Let's see if I have understood this correctly:

You want an assignment of Discount per combination of Parent and YearMonth. I.e. within each such combination there is only one Discount assigned. Right?

Further, you want a static assignment. I.e. the Discount for a specific Parent-YearMonth should not change just because the user makes a selection in some other field, e.g. Date or Child. Right?

If so, I'd say that an IntervalMatch in the script is the correct solution - but the IntervalMatch should be made on the aggregated budget number. Hence:

Data:
Load * From Table1 (…) ;

Left Join (Data) Load
   Parent, Year, Month,
   Sum(Budget) as AccumulatedBudget
   Resident Data Group By Parent, Year, Month;

Discounts:
Load * From Table2 (…) ;

Left Join (Data) IntervalMatch (AccumulatedBudget, Code)
Load From, To, Code Resident Discounts;

Left Join (Data)
Load * Resident Discounts;
Drop Table Discounts;

HIC

[#8]

Thank you for answer Henric Cronström,

    If i not wrong this solution mean I must select Parent, Year and month so result will be correct right? If I select year only and show all parent child and month? that why I should find dynamic solution.

Thank you

[Digvijay_Singh]

If you want the change of discount percentage based on your selections(like If you select only Parent 'A' and 2015, and you dynamically want to add up all budget values matching with this combination disregarding month and child and then want to apply discount percentage), It seems very very complex to me.

But if you would want to add more dimensions like Year and Month along with Parent, it won't require much change in the previous solution suggested, you can add Year and Month with Parent in Group By statement and after load.

But I suspect if you have something related to  the first thing I mentioned above.

[#10]

Thank you Digvijay Singh,

The bad news as I want is the first thing