
Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
omar bensalem Jun 5, 2017 6:29 AM (in response to Damiano Uka)If your expression is sum(kpi); to do the cumul :
rangesum(above(sum(kpi),0,rowno())
Hope this helps

Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
Damiano Uka Jun 5, 2017 8:44 AM (in response to omar bensalem )Thank you Omar,
the problem is that the kpi is calculated merging two tables.. but the merge doesn't work properly
my expression is Count(idOrder)/Qty (per item and per Year).. so it's something i don't know how to save and where to store it, in a column? I don't know... Actually is something like a matrix division with a column vector, where the matrix is [Production Year]x[Claim Year] * Purchased (where year of purchasing = Production Year)....I'm stucked totally in this

Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
Robin Hausdörfer Jun 6, 2017 5:32 AM (in response to Damiano Uka)I think we cannot help you as long as you don't prepare a sample...

Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
Damiano Uka Jun 6, 2017 6:07 AM (in response to Robin Hausdörfer)Thank you Robin, I don't know exactely if this sample is sufficient for you, in case let me know, i just prepare it like this
My kpi: Count(idOrder)/Qty
in the script I don't know how to write it as are two different tables... actually, what I want to compute is the ratio between of the number of defected item (in year X) on the Quantity of that item in that year, per each year... In 1 table I have the yearly quantity purchased for a specific item, in the other table I have to sum the number of claims.. How can I do it? I think that setting things like this is creating me some complexities. Do you know how to o it ?

Sample index.qvf 192.0 K

Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
Robin Hausdörfer Jun 6, 2017 11:27 AM (in response to Damiano Uka)if Production Year is the same as Purchasing Year, then you need a syntethic key or a link table containing
 ITEM
and
 Production/Purchasing Year
for getting the quantity per item per year...
Alternatively you could use P() function, but I think the link table solution is easier to understand for beginners...
So please upload your 2 Input files so that I can rebuild the data model...
Regards

Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
Damiano Uka Jun 6, 2017 11:32 AM (in response to Robin Hausdörfer)Thank you Robin,
these are the sample files in excel I used.
I understand the chance of linking the table creating a synthetic key, but what if I want, once the model is built, to filter by Item? I won't be able to aggregate all the defects together, but I will have itemyear, right?
Thank you for your help,regards
Damiano

Sample index.qvf 192.0 K

Database.xls 44.5 K

Purchased.xlsx 14.4 K

Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
Robin Hausdörfer Jun 6, 2017 12:31 PM (in response to Damiano Uka)ok this is a very fast try of mine, please have a look if the data is ok, then we could talk about the line chart...

Sample index.qvf 192.0 K

Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
Damiano Uka Jun 7, 2017 4:35 AM (in response to Robin Hausdörfer)Good Morning Robin,
thank you so much for your help. It works fine, the calculations are right!
I was trying to create the cumulative line graph but It gives me a problem in the expressions. I don't understand why.
How can we solve that issue?







Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
Robin Hausdörfer Jun 5, 2017 8:53 AM (in response to omar bensalem )or perhaps:
rangesum(before(sum(kpi),0,columnno())
in pivot table with horizontal dimensions...

Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
Damiano Uka Jun 6, 2017 5:15 AM (in response to Robin Hausdörfer)it doesn't work in the expression, because my KPI is
Count(idOrder)/Qty
in the script I don't know how to write it as are two different tables... actually, what I want to compute is the ratio between of the number of defected item (in year X) on the Quantity of that item in that year, per each year... In 1 table I have the yearly quantity purchased for a specific item, in the other table I have to sum the number of claims.. How can I do it? I think that setting things like this is creating me some complexities. Do you know how to o it ?



Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
Robin Hausdörfer Jun 7, 2017 3:14 PM (in response to Damiano Uka)I think I got it ... expression:
aggr(rangesum(above(Count([ID ordine]),0,RowNo()))/sum(total <[$Production Year]> Qty),([$Production Year],(NUMERIC, ASCENDING)),([Claim Year],(NUMERIC, ASCENDING)))
> it's a combination of Using interrecord functions: swap xaxis and legend in charts and Running totals per dimension (RangeSum(), sortable Aggr()...)

Sample index.qvf 192.0 K

Re: Cumulative Percentage sum of a row obtained by a pivot table in Qlik Sense
Damiano Uka Jun 8, 2017 5:45 AM (in response to Robin Hausdörfer)Dear Robin,
it works perfectly. Thank you so much for your help. Your explaination in the other threads are so clear, thank you again!
Regards
Damiano
