topic Re: SUM IF Comparation with Set Analysis in QlikView

SUM IF Comparation with Set Analysis

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

Hi there

I hope that you can help me; I have a table with the budget and sales information and I want to create a pivot table that has to show only the products with an Over Sales, that is, products that exceeded the budget but only the amount of the difference between the Sales and the budget, for example, if the budget of a product is 100 USD and the sales were 120 USD, I want the table to only show 20 USD which is the excess of sales. My expresion so far is:

Where:

Sales_Order_Year = Is the dinamic Year Selected

Budget = It is an indicator for those products that do have a sales budget

Source = Source or data, for Sales the ERP System for Budget it is a variable $(Versus) that it is a version selected of the budget

IF((
//SALES
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}>} Domestic_Amount_USD))
/
// BUDGET
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source={'$(=$(Versus))'}>} Bgt_Amount_USD))
) >1,
(
//SALES
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}>} Domestic_Amount_USD))
-
// BUDGET
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source={'$(=$(Versus))'}>} Bgt_Amount_USD))
)
, 0)

My Table

PRODUCT	Bgt	Sales	This is the correct ammount	This is what I´m getting on the pivot table
Total	23,587.91	24,008.78	4,605.66	420.87
A	5770.20	6787.64	1017.43	1017.43
B	666.47	1629.67	963.20	963.20
C	447.03	1350.32	903.29	903.29
D	814.09	1196.14	382.05	382.05
E	404.16	727.70	323.54	323.54
F	223.20	533.62	310.42	310.42
G	229.06	400.83	171.77	171.77
H	420.75	559.62	138.87	138.87
I	81.00	200.77	119.78	119.78
J	248.40	322.57	74.16	74.16
K	93.97	150.09	56.12	56.12
L	110.47	155.16	44.68	44.68
M	160.94	189.30	28.36	28.36
N	88.65	108.95	20.29	20.29
O	124.93	144.73	19.79	19.79
P	101.89	110.01	8.12	8.12
Q	44.65	52.75	8.10	8.10
R	39.29	47.22	7.93	7.93
S	131.07	138.82	7.75	7.75
T	188.12	186.82	0.00	-1.30
U	20.55	13.01	0.00	-7.54
V	12.00	4.20	0.00	-7.80
W	12.02	0.00	0.00	-12.02
X	14.25	0.00	0.00	-14.25
Y	71.00	53.29	0.00	-17.71
Z	150.48	125.85	0.00	-24.63
AA	41.04	12.32	0.00	-28.72
AB	2829.03	2760.87	0.00	-68.16
AC	128.73	40.28	0.00	-88.45
AD	384.52	282.32	0.00	-102.19
AE	117.73	8.57	0.00	-109.16
AF	318.08	205.62	0.00	-112.46
AG	1061.31	900.35	0.00	-160.97
AH	183.54	0.00	0.00	-183.54
AI	1886.67	1693.69	0.00	-192.98
AJ	439.92	193.42	0.00	-246.50
AK	627.00	108.45	0.00	-518.55
AL	804.60	12.87	0.00	-791.73
AM	4097.10	2600.96	0.00	-1496.13

The table I want to obtain is:

PRODUCT	Over Sales
Total	4,605.66
A	1017.43
B	963.20
C	903.29
D	382.05
E	323.54
F	310.42
G	171.77
H	138.87
I	119.78
J	74.16
K	56.12
L	44.68
M	28.36
N	20.29
O	19.79
P	8.12
Q	8.10
R	7.93
S	7.75

Thanks in advanced for your help.

Regards,

Edgar.

Re: SUM IF Comparation with Set Analysis

tresB — Wed, 10 Jun 2020 07:06:20 GMT

So it's kind of avoiding neg values from Sales-Budget. Try like:

=RangeMax( SalesExp - BudgetExp, 0)

Re: SUM IF Comparation with Set Analysis

EdgarMM — Wed, 10 Jun 2020 13:43:01 GMT

Thanks for your kind response, I tried using RangeMax as suggested but I'm getting the same result, the pivot table resultant is not showing the negative results anymore, but in the total of the column, it looks like the negatives are been considered. My table result after using RangeMax:

PRODUCT	RangeMax
Total	420.87
A	1017.43
B	963.20
C	903.29
D	382.05
E	323.54
F	310.42
G	171.77
H	138.87
I	119.78
J	74.16
K	56.12
L	44.68
M	28.36
N	20.29
O	19.79
P	8.12
Q	8.10
R	7.93
S	7.75

Regards,

Edgar.

Re: SUM IF Comparation with Set Analysis

EdgarMM — Wed, 17 Jun 2020 14:51:00 GMT

Hi there

I found a way to solve this using the aggr function before the expression :

=(sum(aggr(IF((
//SALES
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}>} Domestic_Amount_USD))
/
// BUDGET
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source={'$(=$(Versus))'}>} Bgt_Amount_USD))
) >1,
(
//SALES
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}>} Domestic_Amount_USD))
-
// BUDGET
(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source={'$(=$(Versus))'}>} Bgt_Amount_USD))
)
, 0), Product).

Regards,

Edgar.