topic Re: Sum Up Top N in QlikView

Sum Up Top N

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

HI every one ,

I have below sample data:

REPORT_DATE	month	Site_ID	outage
6/1/2016	2016-06	1	120
6/2/2016	2016-06	1	500
6/1/2016	2016-06	2	300
7/1/2016	2016-07	4	120
7/2/2016	2016-07	3	500
7/2/2016	2016-07	3	300

I want to sum up Top N Site Outage per month (first i should calculate site outage per month then sum up top N site outage per month) . I have to do it in front end (in expression) not in script.

I appreciate if someone help me.

Re: Sum Up Top N

jyothish8807 — Thu, 09 Feb 2017 05:26:41 GMT

Hi Nassim,

What do you mean by Top N over here ? N could be any value ?

BR,

Re: Sum Up Top N

tresB — Thu, 09 Feb 2017 05:37:23 GMT

Try like:

=Sum(Aggr(If(Rank(Sum(outage))<=vTop, Sum(outage)), Site_ID))

Re: Sum Up Top N

qolkaram — Thu, 09 Feb 2017 05:52:30 GMT

thanks Tresesco , but need Top N per Month , actually finally i need a chart like below

Re: Sum Up Top N

tresB — Thu, 09 Feb 2017 06:09:39 GMT

PFA

Re: Sum Up Top N

qolkaram — Thu, 09 Feb 2017 07:05:27 GMT

Sorry Tressco i couldn't reply on your last post ,

by the way , i don't want to add site_id in Dimension ,( As you can see in the chart there is two bar for each month total monthly outage and top25Site outage ) so now how can i first calculate monthly sum of outage per side_id and then sum up Outage of top N site_id .

Re: Sum Up Top N

qolkaram — Thu, 09 Feb 2017 07:36:15 GMT

some actual data attached

Re: Sum Up Top N

tresB — Thu, 09 Feb 2017 07:44:28 GMT

Not really sure about your requirement.

Re: Sum Up Top N

qolkaram — Thu, 09 Feb 2017 08:08:30 GMT

during last week i tried to find a solution but still i am confused .

Re: Sum Up Top N

tresB — Thu, 09 Feb 2017 08:35:41 GMT

Now since you are here, please try more on clarifying your requirement better.

Re: Sum Up Top N

qolkaram — Thu, 09 Feb 2017 09:03:01 GMT

here is my requirement:

1- calculate total monthly outage for each site_id

2- select top 25 side_id with highest outage for each month .

3- sum up the 25 outage for each month and plot it in a bar chart

Re: Sum Up Top N

tresB — Thu, 09 Feb 2017 09:08:01 GMT

Do you want to see single bar for each month?

Re: Sum Up Top N

sdmech81 — Thu, 09 Feb 2017 09:44:56 GMT

Hi May be like this:

Add dimension I graph as =month(Date(Date#(month,'YYYY-MM')))

and expression as =Aggr(Rank(sum(outage),25),Site_ID)

Plss try and let me know..

Re: Sum Up Top N

qolkaram — Thu, 09 Feb 2017 15:07:30 GMT

yes,like the chart attached in my previews post

Re: Sum Up Top N

qolkaram — Thu, 09 Feb 2017 15:26:07 GMT

there is a meesage "No Data To display" on the chart

Re: Sum Up Top N

qolkaram — Mon, 13 Feb 2017 08:59:33 GMT

can any one help me to overcome to this issue??