topic Re: Partial sum based on rank in QlikView

Partial sum based on rank

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

i have attached a QVD (kindly refer it ) in that i want to display the rank for top 5 and followed and partial sum for both and sum for others

input

and i want to display like this

(made using excel)

thank you in advance

Re: Partial sum based on rank

sunny_talwar — Thu, 29 Dec 2016 13:19:44 GMT

Something like this?

Re: Partial sum based on rank

eromiyasimon — Thu, 29 Dec 2016 13:22:19 GMT

yes like that only, but here in your result i can see the order of the rank has been changed also i want to display the rank in one column as i shown in excel screenshot

Re: Partial sum based on rank

sunny_talwar — Thu, 29 Dec 2016 13:25:55 GMT

Here you go

Re: Partial sum based on rank

sunny_talwar — Thu, 29 Dec 2016 13:27:36 GMT

Further enhancements

Re: Partial sum based on rank

eromiyasimon — Thu, 29 Dec 2016 14:42:43 GMT

It Worked,

at first thanks a lot,

No one cannot unveil this type of riddle, unless its Sunny,

i replaced my column names in that query and its worked like charm,

but it will take some decades to understand whats going on inside that query,

its not a query its sorcery.

Many thanks

Re: Partial sum based on rank

eromiyasimon — Fri, 30 Dec 2016 05:08:08 GMT

Hi sunny everything fine for the values i gave but i have to get some for an another column which is showing the same sum for every 5

attached the file and Screen shot for reference,

kindly help this out to find, thank you in advance

in this i want the sum of s2 column to be proper for every 5

Re: Partial sum based on rank

sunny_talwar — Fri, 30 Dec 2016 10:22:04 GMT

This?

Expression:

Pick(Dim,

Sum(s2),

Sum({<Name = {"=Rank(Sum(Sales)/Sum(TOTAL Sales)) < 6"}>} s2),

Sum(s2),

Sum({<Name = {"=Rank(Sum(Sales)/Sum(TOTAL Sales)) >= 6 and Rank(Sum(Sales)/Sum(TOTAL Sales)) < 11"}>} s2),

Sum({<Name = {"=Rank(Sum(Sales)/Sum(TOTAL Sales)) >= 11"}>} s2))