Summing different dimensions based on the critera or a common dimension

[jmcdermott]

Good afternoon,

I am trying to  sum information in set analysis based on if the "J_FormItemCategory" is either Finished Goods or Merchandise.  If it is Finished Goods then I want the sum of field " matl_cost", if "J_FormItemCategory" is "Merchandise" then I want sum of field "unit_code" times 105%.  In my formula below I am getting an error message.

if(sum({<J_FormItemCategory='Finished Goods'>}matl_cost),if(sum({<J_FormItemCategory='Merchandise'>}unit_cost*1.05)))

Thanks,

Josh

[Taoufiq_Zarra]

@jmcdermott  if u can share a sample data and the expected output

[jmcdermott]

Good afternoon, here is some sample data

J_FormItemCategory item matl_cost unit_cost qty Expected outcome
Finished Goods FG Part#1 4023.65 4526.42 10 40,236.50
Finished Goods FG Part#2 5820.89 6486.91 4 23,283.56
Finished Goods FG Part#3 3461.75 4031.47 7 24,232.25
Finished Goods FG Part#4 2275.34 2766.05 8 18,202.72
Finished Goods FG Part#5 1400.92 1575.35 9 12,608.28
Finished Goods FG Part#6 2987.06 4967.49 6 17,922.36
Finished Goods FG Part#7 604.55 908.52 3 1,813.65
Finished Goods FG Part#8 604.53 890.3 2 1,209.06
Finished Goods FG Part#9 1790.93 2083.84 1 1,790.93
Merchandise Merch Part#1 15.88 16.67 10 175.04
Merchandise Merch Part#2 9.19 9.65 6 60.80
Merchandise Merch Part#3 16.58 17.41 7 127.96
Merchandise Merch Part#4 16.99 17.84 4 74.93
Merchandise Merch Part#5 15.09 15.84 1 16.63
Merchandise Merch Part#6 9.79 10.28 9 97.15
Merchandise Merch Part#7 12.83 13.47 2 28.29
Merchandise Merch Part#8 8.73 9.17 3 28.89
Merchandise Merch Part#9 15.75 16.54 5 86.84
Merchandise Merch Part#10 15.09 15.84 8 133.06

if(sum({<J_FormItemCategory='Finished Goods'>}matl_cost*qty),if(sum({<J_FormItemCategory='Merchandise'>}(unit_cost*qty)*1.05)))
this set analyisis formula doesn't work

Below in excel

J_FormItemCategory	item	matl_cost	unit_cost	qty	Expected outcome
Finished Goods	FG Part#1	4023.65	4526.42	10	40,236.50
Finished Goods	FG Part#2	5820.89	6486.91	4	23,283.56
Finished Goods	FG Part#3	3461.75	4031.47	7	24,232.25
Finished Goods	FG Part#4	2275.34	2766.05	8	18,202.72
Finished Goods	FG Part#5	1400.92	1575.35	9	12,608.28
Finished Goods	FG Part#6	2987.06	4967.49	6	17,922.36
Finished Goods	FG Part#7	604.55	908.52	3	1,813.65
Finished Goods	FG Part#8	604.53	890.3	2	1,209.06
Finished Goods	FG Part#9	1790.93	2083.84	1	1,790.93
Merchandise	Merch Part#1	15.88	16.67	10	175.04
Merchandise	Merch Part#2	9.19	9.65	6	60.8
Merchandise	Merch Part#3	16.58	17.41	7	127.96
Merchandise	Merch Part#4	16.99	17.84	4	74.93
Merchandise	Merch Part#5	15.09	15.84	1	16.63
Merchandise	Merch Part#6	9.79	10.28	9	97.15
Merchandise	Merch Part#7	12.83	13.47	2	28.29
Merchandise	Merch Part#8	8.73	9.17	3	28.89
Merchandise	Merch Part#9	15.75	16.54	5	86.84
Merchandise	Merch Part#10	15.09	15.84	8	133.06

[Taoufiq_Zarra · Accepted solution]

May be :

=if(J_FormItemCategory='Finished Goods',sum(matl_cost*qty),if(J_FormItemCategory='Merchandise',sum((unit_cost*qty)*1.05)))

[Taoufiq_Zarra]

[Saravanan_Desingh]

One solution is.

tab1:
LOAD * INLINE [
    J_FormItemCategory, item, matl_cost, unit_cost, qty, Expected outcome
    Finished Goods, FG Part#1, 4023.65, 4526.42, 10, "40,236.50"
    Finished Goods, FG Part#2, 5820.89, 6486.91, 4, "23,283.56"
    Finished Goods, FG Part#3, 3461.75, 4031.47, 7, "24,232.25"
    Finished Goods, FG Part#4, 2275.34, 2766.05, 8, "18,202.72"
    Finished Goods, FG Part#5, 1400.92, 1575.35, 9, "12,608.28"
    Finished Goods, FG Part#6, 2987.06, 4967.49, 6, "17,922.36"
    Finished Goods, FG Part#7, 604.55, 908.52, 3, "1,813.65"
    Finished Goods, FG Part#8, 604.53, 890.3, 2, "1,209.06"
    Finished Goods, FG Part#9, 1790.93, 2083.84, 1, "1,790.93"
    Merchandise, Merch Part#1, 15.88, 16.67, 10, 175.04
    Merchandise, Merch Part#2, 9.19, 9.65, 6, 60.8
    Merchandise, Merch Part#3, 16.58, 17.41, 7, 127.96
    Merchandise, Merch Part#4, 16.99, 17.84, 4, 74.93
    Merchandise, Merch Part#5, 15.09, 15.84, 1, 16.63
    Merchandise, Merch Part#6, 9.79, 10.28, 9, 97.15
    Merchandise, Merch Part#7, 12.83, 13.47, 2, 28.29
    Merchandise, Merch Part#8, 8.73, 9.17, 3, 28.89
    Merchandise, Merch Part#9, 15.75, 16.54, 5, 86.84
    Merchandise, Merch Part#10, 15.09, 15.84, 8, 133.06
];

[Saravanan_Desingh]

Dimension: J_FormItemCategory

Expression:

Pick(Match(J_FormItemCategory,'Finished Goods','Merchandise'),
Sum(matl_cost*qty),Sum(unit_cost*qty)*1.05)

[jmcdermott]

Thanks for your help, this worked, I added some criteria as well to filter down to specific month I need, below is the formula for others looking at this post

=if(J_FormItemCategory='Finished Goods',sum({<CurrentFiscalYearFlag = {1}, LastMonthFlag = {1}>}(matl_cost*qty)),if(J_FormItemCategory='Merchandise',sum({<CurrentFiscalYearFlag = {1}, LastMonthFlag = {1}>}(unit_cost*qty)*1.05)))