Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be :
=if(J_FormItemCategory='Finished Goods',sum(matl_cost*qty),if(J_FormItemCategory='Merchandise',sum((unit_cost*qty)*1.05)))
@jmcdermott if u can share a sample data and the expected output
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 |
May be :
=if(J_FormItemCategory='Finished Goods',sum(matl_cost*qty),if(J_FormItemCategory='Merchandise',sum((unit_cost*qty)*1.05)))
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
];
Dimension: J_FormItemCategory
Expression:
Pick(Match(J_FormItemCategory,'Finished Goods','Merchandise'),
Sum(matl_cost*qty),Sum(unit_cost*qty)*1.05)
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)))