topic Re: sum in pivot table with RowNo () in QlikView

sum in pivot table with RowNo ()

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

Hello.
I need your kind help.
I have a pivot table, with three dimensions and different expressions.
With two variables, vDataMin and vDataMax, I limit the display of the lines only to those that have a value of <Data> included between these values.
On each line I calculate the number of days between the current line and the next line (column <Tot_Giorni>)
With the following expression

sum (If (Data> = (vDataMin) and Data <= (vDataMax),
if (NextDate <= (vDataMax),
Day,
(VDataMax-Data)
)
)
)

to this value I should add, for each article, the value present only in the first row (Val_Row_1 = 7, for example for article 40101653).
this value is calculated with the following expression
if (RowNo () = 1, sum (Data-vDataMin), 0)

having to use the pivot, in making the sum, while obtaining a correct value on the line (12 instead of 5) I cannot get a total of days for the correct article. In fact, for example, for the first article I always get 195 and not 202 (correct value)
can you suggest me how to write the sum of the columns for the pivot?
thank you in advance.
regards

Re: sum in pivot table with RowNo ()

gf — Wed, 10 Apr 2019 08:20:04 GMT

Hello,
so you want to calculate each Val_Row_1 like in the first row?

Re: sum in pivot table with RowNo ()

francofiorillo — Wed, 10 Apr 2019 11:22:18 GMT

No.
the result I want is the one shown in the "Valore desiderato" column that represents the sum of "Tot_Giorni" and "Val_Row_1 ".
Using RowNo () in the "Val_Row_1" column in a pivot, when I add the expressions of the two columns, even if I get the correct value at the row level (in the example the value 12) the total for Item always returns me 195 and not 202 which is the desired value.
in essence,
// Expression of "Tot_Giorni"
sum (if (NextDate> (vDataMax),
(vDataMax-Data), Day))
+
// Expression of "Val_Row_1
if (Data = Top (Data), sum (Data-vDataMin), 0))
works on the lines but not for the total for Item

Re: sum in pivot table with RowNo ()

gf — Wed, 10 Apr 2019 12:06:11 GMT

For column "Valore desiderato" try
=RangeSum(Tot_Giorni,Val_Row_1)

Re: sum in pivot table with RowNo ()

francofiorillo — Wed, 10 Apr 2019 13:22:52 GMT

No, unfortunately I always get the same unwanted result.
I want the total for Item to be 202 and not 195 in the "Valore desiderato" column

Re: sum in pivot table with RowNo ()

gf — Wed, 10 Apr 2019 13:28:41 GMT

May you share the whole file and i will see what i can do.

Regards.

Re: sum in pivot table with RowNo ()

francofiorillo — Wed, 10 Apr 2019 13:48:05 GMT

here is the file.
I only uploaded some records.
Meanwhile, I thank you for the support you are providing.

Re: sum in pivot table with RowNo ()

francofiorillo — Wed, 10 Apr 2019 13:49:03 GMT

here is the file.
I only uploaded some records.
Meanwhile, I thank you for the support you are providing.

Re: sum in pivot table with RowNo ()

gf — Wed, 10 Apr 2019 13:50:08 GMT

I have to enter username and password?

Re: sum in pivot table with RowNo ()

francofiorillo — Wed, 10 Apr 2019 13:58:08 GMT

admin usr

V1etat0 psw