Exclude Top 1% records from webi report

building reports webintelligence
1

Hello Friends,

Please help me on this requirement. I am having tough time to resolve.

I have a requirement where I need to Exclude Top 1% records from webi report.

First lets try on Top 1 row: We need to exclude this top 1 record and do average of days from 2nd value to till last value) on rest of the records. let me know how to achieve this with ranking or any other function. Greatly appreciated for your help.

Cal-Month Document Days
01/2018 1821 0
01/2018 1822 2
01/2018 1823 0
01/2018 1824 2
01/2018 1834 3
01/2018 1835 0
01/2018 1865 5
01/2018 2576 5
01/2018 2577 0
01/2018 2578 4
01/2018 2579 7

Average is 2.1

ggudapati (BOB member since 2018-05-01)

2

Hi,

With the function rank I can easily calculate the top 1% employee and average sales

Var=If(Rank([Measure])<6;"Hide";"Show") 

FlagVar=If([1Var]=[2Var];1;0)

However I would like to have average and sum of sales of the top employee without having all employees in my report.

When I remove the employee from the block, Average sales its not calculated anymore.
Could you please advise how I can do the calculation with out the employee in the table?

Thanks in advance

[Moderator Edit: Added code formatting - Andreas]

ggudapati (BOB member since 2018-05-01)

3

Hello Friends,

Can you please help me with this issue.&#8203;

ggudapati (BOB member since 2018-05-01)

4

Use ranking functionality. Keep the top 1 based on the new variable that you’ve built.

5

Hi Mark,
Rank is working only if employee number is in the block. as soon as the employee number is removed from block, value is showing blank.
requirement is to show top 1% at country level. Please advise!
my source is Bex Query not universe. please help me.

ggudapati (BOB member since 2018-05-01)

6

Data source doesn’t matter; we’re doing this at report level. :slight_smile:

Add FlagVar to your block (I presume that this is the variable that will tell you what you want to keep and get rid of)

Remove any objects from the block that you don’t want in your final block - is FlagVar still showing correctly?

If it is, then you can rank on FlagVar and keep the top 1 (by count).

7

Hi Mark,

I added FlagVar tru-fal-rank to my block to keep "true"and get rid of 'false"records.

Here are my variables:
Minus_Top_1% (Measure) = ([Total Receipts]*0.99)
Rank-runcnt (Measure) = Rank([CWT Days]+(RunningCount([CWT Days])/ (RunningCount([CWT Days])+0.8 )))
tru-fal-rank (dimension)= If [rank-runcnt] > Round([Minus Top 1%];0) Then “true” Else "false "

This Flagvar is working only when I have one month records. It’s resetting to show both true and false as soon as I am running report for more than 1 month data with different storage locations.

Please find the attached screenshot and let me know how should I make it work to filter out false data when we have more than one block data or more months or more slocations.

please help me Mark.
Capture.JPG

ggudapati (BOB member since 2018-05-01)

8

Brilliant, you’re nearly there. Instead of “true” and “false”, use 1 and 0 and then apply a ranking based on this.

9

Hi Mark, Thanks for the response.

I have changed from “true” and “false” to 1 and 0. Now the filter on the block is not filtering to get the 1’s count.

Please see the attached screenshots and let me know why I am not able to filter 1’s.
Capture.JPG
Capture1.JPG

ggudapati (BOB member since 2018-05-01)

10

Use ranking instead of filtering.