topic Re: Summing different dimensions based on the critera or a common dimension in QlikView

Summing different dimensions based on the critera or a common dimension

jmcdermott — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Summing different dimensions based on the critera or a common dimension

Taoufiq_Zarra — Mon, 08 Jun 2020 20:58:34 GMT

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

Re: Summing different dimensions based on the critera or a common dimension

jmcdermott — Wed, 10 Jun 2020 15:57:15 GMT

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

Re: Summing different dimensions based on the critera or a common dimension

Taoufiq_Zarra — Wed, 10 Jun 2020 16:21:30 GMT

May be :

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

Re: Summing different dimensions based on the critera or a common dimension

Taoufiq_Zarra — Wed, 10 Jun 2020 16:22:34 GMT

Re: Summing different dimensions based on the critera or a common dimension

Saravanan_Desingh — Thu, 11 Jun 2020 02:04:21 GMT

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 ];

Re: Summing different dimensions based on the critera or a common dimension

Saravanan_Desingh — Thu, 11 Jun 2020 02:06:07 GMT

Dimension: J_FormItemCategory

Expression:

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

Re: Summing different dimensions based on the critera or a common dimension

jmcdermott — Mon, 15 Jun 2020 20:13:03 GMT

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