How to eliminate records based on sum(meansure) =0

[Former Member]

Hi,

Please let me know if you have any solutions for the below issue:

I have a Report with the below format:

Date             Mob no    IMEI no    Amount    Unit

08/15/2013    1234    23456         700              1

08/16/2013    1234    23456         -700              -1

09/15/2013    1234    76543         700              1

09/16/2013    4567    88888         800              1

12/30/2013    4567    88888         -900              -1

12/30/2013    5678    98765         6000              0

Basically, the original report should be modified to get the below output.

If the Mob no and IMEI no is same and if the amount is 0 then the entire row should not be displayed.

This report should be modified to the below format:

Date    Mob no    IMEI no    Amount    Unit

09/15/2013    1234    76543    700    1

09/16/2013    4567    88888    800    1

12/30/2013    4567    88888    -900    -1

12/30/2013    5678    98765    6000    0

Can you please let me know any solutions for the below issue. I am using BO WebI XI3.1

Thanks

[Former Member · Accepted solution]

Hi Sindhu,

Create a Measure Variable  "Test" with the deffitinion  "=[Amount] ForAll([Date])".

select the Block,where the problem lies, and apply a block filter  , Drag that variable into Block filter pane build the below condition   "Test  NotEqual to 0"

It will work....

God Luck...

---

Shvia

[Former Member]

Hi Siva/Sanjo,

Thank you so much for your response.

This solutions is working.

Regards

[former_member184543]

Hi Sindhu,

Since you have multiple dimensions in the block, you will need to eliminate them while summing up. Hence use this formula to do so in a variable and add to the block:

=sum([Amount]) forall([Date];[Unit])

Now you will get 0 for the rows which aggregates up to 0 for all combinations of Mob and IMEI.

Then apply filter on the block wherein this variable <>0 and you will get the remaining rows which doesnt add up to 0.

Hope this helps.

Regards,

Sanjo

[jyothirmayee_s]

Hi,

Create a variable in webi @ Flag

= if (Mob No = IMEI No) then 1 else 0.

Apply filter on @ Flag <> 1 and Amount <> 0

Thanks,

Jothi

[Former Member]

Hi Jyothirmayee,

Thanks for your response, here Mob no and IMEI no are not same,

I meant  Mob no and IMEi no is same in 1st and 2 row, then if sum (amount) = 0, then both the rows need to be filtered from WebI.

Please let me know if you have any solutions for this issue.

Thanks

Sindhu

[Former Member]

Hi Sindhu,

Your requirement needs some kind of data processing. So best way to handle it is either in a stored procedure (using cursor) or ETL tool (for loop).

In reporting tool, we can not handle such processing as they are not meant for it.

One of the option in BO XI 3.1 which I can suggest is to use Previous() function. Using this function, you can check value of dimension in previous record to make comparison. So using previous function you can check if 2 rows are having same mobile no., IMEI no. and same amount or not. Based on this condition you can set a flag with Yes or No.

And apply alerter to color them with white font color. But we can not filter them at run time.

I hope this helps.