Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Search instead for
Did you mean:
Specialist III

## Sales - Sum of Quantity based on ParentName and ChildName

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

I'm sorry!

Try this one expression:

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

7 Replies
MVP

Create a pivot table with

Dimensions ParentName and ChildName

and

Expression Sum(Qty)

Select partial sums and total sums.

Regards

Marco

Anonymous
Not applicable

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

Specialist III
Author

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

Anonymous
Not applicable

I'm sorry!

Try this one expression:

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

Specialist III
Author

Dear Elena,

Is this possible to explain you expression here?

Kind regards,

Ishfaque Ahmed

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Community Browser