topic Re: Sales - Sum of Quantity based on ParentName and ChildName in QlikView

Sales - Sum of Quantity based on ParentName and ChildName

engishfaque — Sun, 25 Jan 2015 10:17:09 GMT

Dear All,

I'm trying to evaluate parent and child total quantity with sum of both like required output is given below, but facing an issue.

Sales.qvw and Sales.xlsx is attached with this thread, please find attached.

Required output:

ParentName	ChildName	Qty	Total Qty
NI-R6501-Gadsden35-MF-006-Set3			13
	NI-gadsden35	2	15
	NI-MF-006	7	20
	NI-R6501-ZZ8	4	17
NI-R6501			4
	NI-R6501-ZZ8	1	5
	NI-MF-006	3	7

Expressions:

ParentName Total Qty = Child1 + Child2 + Child3

ChildName1 Total Qty = ParentName Total Qty + Child1

ChildName2 Total Qty = ParentName Total Qty + Child2

ChildName3 Total Qty = ParentName Total Qty + Child3

Note: Please share me script and expressions, I'm using QlikView Personal Edition.

Kind regards,

Ishfaque Ahmed

Re: Sales - Sum of Quantity based on ParentName and ChildName

MarcoWedel — Sun, 25 Jan 2015 10:43:10 GMT

Create a pivot table with

Dimensions ParentName and ChildName

and

Expression Sum(Qty)

Select partial sums and total sums.

Regards

Marco

Re: Sales - Sum of Quantity based on ParentName and ChildName

Anonymous — Sun, 25 Jan 2015 10:48:10 GMT

Hello,

I get this output: can it be ok for you?

I changed your script as follow:

NULLASVALUE ChildName, Qty;

Product:

LOAD ParentName, ChildName, Qty

FROM Sales.xlsx (ooxml, embedded labels, table is Sheet1);

Concatenate

load ParentName, null() as ChildName, null() as Qty

FROM Sales.xlsx (ooxml, embedded labels, table is Sheet1);

LOAD ParentName, Sum(Qty) as Total Resident Product Group By ParentName;

and expressions of pivot table are:

-Qty: just the field Qty

-Total Qty: if(IsNull(ChildName), sum(TOTAL <ChildName> Qty),sum(Qty)+ sum({1}total <ParentName> Qty))

Please, check carefully if selections of your application work well: for example I insert {1} in the last sum because if you click on a specific ChildName yuo can mantain the same row.

Let me know!

KR,

Elena

Re: Sales - Sum of Quantity based on ParentName and ChildName

engishfaque — Sun, 25 Jan 2015 11:14:20 GMT

Dear Elena,

Still issue is coming, when I'm going to select any one parent then It's showing both parents in a pivot table.

Not selected parent should not display.

Kind find attached screenshot.

Kind regards,

Ishfaque Ahmed

Re: Sales - Sum of Quantity based on ParentName and ChildName

Anonymous — Sun, 25 Jan 2015 11:20:57 GMT

I'm sorry!

Try this one expression:

if(IsNull(ChildName), sum(TOTAL <ChildName> Qty),sum(Qty)+ sum({1<ParentName=p(ParentName)>}total <ParentName> Qty))

Re: Sales - Sum of Quantity based on ParentName and ChildName

engishfaque — Sun, 25 Jan 2015 11:39:59 GMT

Dear Elena,

Is this possible to explain you expression here?

Kind regards,

Ishfaque Ahmed

Re: Sales - Sum of Quantity based on ParentName and ChildName

Anonymous — Sun, 25 Jan 2015 11:40:07 GMT

Hi,

Try:

Script:

ParentChild:
LOAD ParentName,
ChildName,
Qty
FROM

(ooxml, embedded labels, table is Sheet1);

LOAD
ParentName,
Sum(Qty) as SumQty
Resident ParentChild
Group By ParentName;

Expression:

sum(Qty)+ sum(SumQty)

Regards

Neetha

Re: Sales - Sum of Quantity based on ParentName and ChildName

Anonymous — Sun, 25 Jan 2015 12:08:52 GMT

Doesn't it work again?

I divided the simple sum into two parts, one for parent, in order to get to total row, and one for child, in order to consider your logic (suce as ChildName1 Total Qty = ParentName Total Qty + Child1).

To do this, I had to have a fictive child, added in script, displayed as the first child (tab Sort)

So, if the child is the fictive one (null value, first row), simply sum Qty disregarding Child (but maybe you can eliminate TOTAL <ChildName>, because it seems work even without it), and this one gives you the total.

If Child is not the fictive one, so it's one of yours, you have to sum qty of this child (sum(qty)) and of its parent that is the second part of the expression. I use {1} beacuse without it, formula didn't work if you selected a child, but in this way (as you appointed) expression didn't take in account any selection. So, if you want that every selection changes the result, you have to force {1} adding p() function for evrey fields you want: for these field {1} is not applied.

I dont' know if it's clear...

Thanks