11 Replies Latest reply on Dec 9, 2016 7:08 AM by Yuri Fal

# LOD - excluding elements (only the hours of the element that breaches a certain TH)

Been struggling with this LOD question from a customer, he wants to look at KPI values after excluding the elements (dimension = telco cells) for the hours when a certain measure (EG_CLUNDROP) breaches a certain TH (and only for that hour, not excluding the dimension entirely from the analysis).

I tried using the below formula, which works for a selection of Cells but when looking at all of them doesn't produce the correct result.

IF SUM({ INCLUDE [Name (Child)], [Hour] : SUM([EG_CLSDCCH_CLUNDROP])}) > 50 then "exclude"

else "keep"

end

Tried then adding LOD = SUM({ INCLUDE [Cell],[Hour] : ZN(SUM([EG_CLUNDROP]))}) in filter or selecting a condition for elements that have LOD > 50 ... that doesn't seem to work

Tried then:

IF { INCLUDE [Cell],[Hour] : ZN(SUM([EG_CLUNDROP]))} > 100 then 0

else [EG_CLUNDROP]

END

which works ... however the point is to use it in KPIs that don't contain that measure.

This is what I need:

IF { INCLUDE [Name (Child)],[Hour] : ZN(SUM([EG_CLSDCCH_CLUNDROP]))} > 50 then  0 // if the value for a cell is > 50 then discard it and add 0 (no contribution to KPI)

else [2G Speech Call Setup Failure Rate [%]]] // else just show the KPI with the element's contribution to KPI

END

Any help is more than welcome.

Cheers,

• ###### 1. Re: LOD - excluding elements (only the hours of the element that breaches a certain TH)

Any ideas or similar calculations anyone has done before?

I ran out of ideas, so as mentioned before any help is appreciated

Cheers

• ###### 2. Re: LOD - excluding elements (only the hours of the element that breaches a certain TH)

This is complex enough that we really need a sample packaged workbook to help you work it out. Attach one and ping me and I'll take a look.

--Shawn

• ###### 3. Re: LOD - excluding elements (only the hours of the element that breaches a certain TH)

Hi Mihai,

If your [2G Speech Call Setup Failure Rate [%]]] calculation

is an Aggregate (or even a Table Calc), then you may try this:

if MIN( { INCLUDE [Name (Child)],[Hour] : ZN(SUM([EG_CLSDCCH_CLUNDROP]))} ) > 50 then  0

else [2G Speech Call Setup Failure Rate [%]] end

(Blindly) hope this could help.

If not, a workbook is highly wanted

(or a mockup using Sample Superstore).

Yours,

Yuri

• ###### 4. Re: LOD - excluding elements (only the hours of the element that breaches a certain TH)

Thanks guys, attached an anonymised sample workbook.

To summarise again in this screen shot there are 2 cells with >50 behaviour. One has all values, for every hour > 50 so should be excluded entirely, the other one (blue) only has a spike (at 4 am) and only that spike should be excluded when calculating the 2G Speech Failure Rate.

Kind regards,

• ###### 5. Re: LOD - excluding elements (only the hours of the element that breaches a certain TH)

Unless I'm misunderstanding I think all you need to do is put a MAX() CNDROP on the filter shelf like this:\

In your sample data the blue line will disappear completely because it only has one point that is now being filtered out.

--Shawn

• ###### 6. Re: LOD - excluding elements (only the hours of the element that breaches a certain TH)

Hi Shawn, thanks but that will be incorrect behaviour 1) I don't want to remove all values for that cell, just the hour with the value > 50 and 2) I want to see the KPI below adapted to the value filtered (KPI value minus the value at that time for that cell)

Cheers,

• ###### 7. Re: LOD - excluding elements (only the hours of the element that breaches a certain TH)

Hi Mihai,

Yours,

Yuri

• ###### 8. Re: LOD - excluding elements (only the hours of the element that breaches a certain TH)

Thanks very much Yuri... However I think you missed something:

This is the original formula for that KPI

1 -((1 - SUM(ZN([EG_CLSDCCH_CNDROP])) / SUM(ZN([EG_CLSDCCH_CMSESTAB]))) ...

this is yours:

1-((1 - SUM(ZN([YF : EG_CLSDCCH_CLUNDROP])) / SUM(ZN([EG_CLSDCCH_CMSESTAB])))

CNDROP and CLUNDROP are different measures. That KPI does not contain the CLUNDROP (unfortunately).

As a side telco explanation, they want to look at KPI (Success Call Rate or Speech Drop Rate or whatever) excluding cells that have a certain behaviour or don't have a significant volume of traffic so they are insignificant for their analysis.

e.g. Drop Call Rate for Cells that have more than 150 Calls per hour. If the cell is not used the DCR is irrelevant.

Thanks again very much for the help!

• ###### 9. Re: LOD - excluding elements (only the hours of the element that breaches a certain TH)

Hi Mihai,

My fault (don't be working late tonight :-).

If the aim is to exclude certain Cells-Hours from the view,

then a Conditional Aggregate Filter could help.

Both INCLUDE and FIXED LOD variants are there to compare.

The INCLUDE one is preferable in most cases,

since it is calculated at the VizLOD (fewer # Marks)

and doesn't require the Context to be applied.

If one needs to have those Cells-Hours

as Sets / Bins (aka Dimensions),

then the FIXED LOD is to the rescue.

Please find the attached (with mods).

Hope it could help.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 10. Re: LOD - excluding elements (only the hours of the element that breaches a certain TH)

Thanks very much Yuriy, that looks perfect. I will test in production but I am pretty sure it will work.

I was so close, but you took it one step further. Brilliant, cheers!

• ###### 11. Re: LOD - excluding elements (only the hours of the element that breaches a certain TH)

Mihai, you're welcome.