5 Replies Latest reply on Nov 7, 2018 8:42 AM by Deepak Rai

Excluding Certain Values from an LoD expression

Hi all,

I've seen this asked before but the suggested action didn't work when I tried it.

I have a few categories in a dashboard, and I'm looking to find the relative share within each of them.

This works fine when everything is included.

My basic formula is to have a "Total Records" formula as follows: {fixed  [Category]: sum([Number of Records])}

I then apply a formula for the share to be: SUM([Number of Records])/SUM([Total Records]). Generally this works fine.

However, a problem then arises when I want to apply a filter beyond this. For example, I'd like to know what the Total Records are if I exclude a certain member within a category from the result.

I have tried an if statement on the Total Records formula of:

if [Item]="variable to omit" then 0 ELSE

{fixed  [Category]: sum([Number of Records])}

END

This had the undesired result of increasing the Total Records to a huge amount for some reason (as opposed to shrinking it).

Can anyone give me any advice as to what I did wrong?

Many thanks,

Eoghan

• 1. Re: Excluding Certain Values from an LoD expression

{fixed  [Category]: sum([Number of Records])} This is OK

%Share

SUM({fixed  [Category]: sum([Number of Records])})/SUM{sum([Number of Records])} Should Return Share percentage

If you remove say Furniture from the category in this Formula then  using this you will get NULL for Furniture and Sum for Each of Other Categories like Technology, Appliances Etc

{fixed  [Category]: sum(If Category<>"Furniture" THEN [Number of Records] END)}

Let me know

Thanks

Deepak

1 of 1 people found this helpful
• 2. Re: Excluding Certain Values from an LoD expression

Hi Eoghan,

You are also able to nest logical statements within the LOD, have you tried the following?

{fixed  [Category]: sum(if [Item]="variable to omit" then 0 ELSE)}

It would be easier to help you if you attach a sample workbook, to know exactly what you are after.

Cheers,

Sasha

• 3. Re: Excluding Certain Values from an LoD expression

Worked a treat kind sir. This solved my problem.

• 4. Re: Excluding Certain Values from an LoD expression

Thanks Sasha, I shall give that a try as a method also for future reference.

• 5. Re: Excluding Certain Values from an LoD expression