Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jmcdermott
Contributor III
Contributor III

Summing different dimensions based on the critera or a common dimension

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

1 Solution

Accepted Solutions
Taoufiq_Zarra

 

May be :

 

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

 

 

 

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

7 Replies
Taoufiq_Zarra

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
jmcdermott
Contributor III
Contributor III
Author

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_FormItemCategoryitemmatl_costunit_costqtyExpected outcome
Finished GoodsFG Part#14023.654526.421040,236.50
Finished GoodsFG Part#25820.896486.91423,283.56
Finished GoodsFG Part#33461.754031.47724,232.25
Finished GoodsFG Part#42275.342766.05818,202.72
Finished GoodsFG Part#51400.921575.35912,608.28
Finished GoodsFG Part#62987.064967.49617,922.36
Finished GoodsFG Part#7604.55908.5231,813.65
Finished GoodsFG Part#8604.53890.321,209.06
Finished GoodsFG Part#91790.932083.8411,790.93
MerchandiseMerch Part#115.8816.6710175.04
MerchandiseMerch Part#29.199.65660.8
MerchandiseMerch Part#316.5817.417127.96
MerchandiseMerch Part#416.9917.84474.93
MerchandiseMerch Part#515.0915.84116.63
MerchandiseMerch Part#69.7910.28997.15
MerchandiseMerch Part#712.8313.47228.29
MerchandiseMerch Part#88.739.17328.89
MerchandiseMerch Part#915.7516.54586.84
MerchandiseMerch Part#1015.0915.848133.06

 

Taoufiq_Zarra

 

May be :

 

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

 

 

 

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Taoufiq_Zarra

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
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)

commQV12.PNG

jmcdermott
Contributor III
Contributor III
Author

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