Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I'm sorry!
Try this one expression:
if(IsNull(ChildName), sum(TOTAL <ChildName> Qty),sum(Qty)+ sum({1<ParentName=p(ParentName)>}total <ParentName> Qty))
Create a pivot table with
Dimensions ParentName and ChildName
and
Expression Sum(Qty)
Select partial sums and total sums.
Regards
Marco
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
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
I'm sorry!
Try this one expression:
if(IsNull(ChildName), sum(TOTAL <ChildName> Qty),sum(Qty)+ sum({1<ParentName=p(ParentName)>}total <ParentName> Qty))
Dear Elena,
Is this possible to explain you expression here?
Kind regards,
Ishfaque Ahmed
Hi,
Try:
Script:
ParentChild:
LOAD ParentName,
ChildName,
Qty
FROM
(
LOAD
ParentName,
Sum(Qty) as SumQty
Resident ParentChild
Group By ParentName;
Expression:
sum(Qty)+ sum(SumQty)
Regards
Neetha
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