1 2 Previous Next 26 Replies Latest reply on Apr 9, 2017 1:06 AM by Segun Aiyegbusi

# LEVEL OF DETAIL

hi . I need to incorporate table calculation and if statement for countd in an LOD.

COUNTD( IF {FIXED [Month]: SUM([Actual Spend])})/TOTAL(SUM([Actual Spend])) > ([%Spend]) THEN [Month] END).

Any help would be appreciated.

• ###### 1. Re: LEVEL OF DETAIL

What version of Tableau are you using ?

What error are you getting ?

Can you supply some dummy data and an explanation of what you're trying to do ?

1 of 1 people found this helpful
• ###### 2. Re: LEVEL OF DETAIL

Many thanks, chris. I am using the latest version of Tableau - 10.0.8.

The reason why I have incorporated table calculation and if statement for a LOD Countd is because of an error details -  "Level of details expression cannot contain table calculations or the attr function".

What I am trying to achieve from the below screenshot to count the numbers of month that have exceeded  Avg.%spend.

In achieving this, I have in the in the 5th column a table calculation that has been derived using this calculation: SUM([Actual Spend])/TOTAL(SUM([Actual Spend]))

I now want to further calculate the count of the month that have exceeded Avg.%Spend. However, using  - IF [1 SUM([Actual Spend]])/TOTAL(SUM([Actual Spend]]))] > AVG([%Spend]) THEN COUNTD([Month]) end - which is giving a grand total of 12.

Reading from the solution in the community the other possible way is to use an LOD expression using - COUNTD( IF  { FIXED [Month] : ([1 SUM([Actual Spend]])/TOTAL(SUM([Actual Spend]]))])} > ([%Spend]) THEN [Month] END) - but  I am getting "Level of details expression cannot contain table calculations or the attr function"

Please see the attached packaged workbook.

1 of 1 people found this helpful
• ###### 3. Re: LEVEL OF DETAIL

Segun Aiyegbusi wrote:

Many thanks, chris. I am using the latest version of Tableau - 10.0.8.

Latest version is now 10.2, but don't worry Tableau releases new versions very frequently

Segun Aiyegbusi wrote:

What I am trying to achieve from the below screenshot to count the numbers of month that have exceeded  Avg.%spend.

Please see the attached packaged workbook.

Try the attached, check out Sheet 2 & 3 and Calculation 1 and 2

2 of 2 people found this helpful
• ###### 4. Re: LEVEL OF DETAIL

Many thanks, Chris... All done!

1 of 1 people found this helpful
• ###### 5. Re: LEVEL OF DETAIL

Maybe you should mark my answer as correct rather than your own

1 of 1 people found this helpful
• ###### 6. Re: LEVEL OF DETAIL

Sorry, mate. Wasn't looking

1 of 1 people found this helpful
• ###### 7. Re: LEVEL OF DETAIL

Hi Chris,

I was also hoping to apply the same LOD principle to a much larger database. However, I think it's much complex as I have more filters in the mix. I have tried also adding other dimensions into  Fixed LOD to no avail. Would it be possible that I am missing something.

• ###### 8. Re: LEVEL OF DETAIL

I can't see what's wrong with that, what a re you aiming for ?

• ###### 9. Re: LEVEL OF DETAIL

Thanks, Chris for your response. I am aiming apply the same LOD calculation in the last Twbx extract I sent but getting a  count 9 instead of a count of 3.

Given that the first Twbx I sent  is a small extract of the second Twbx  with a lot more filters in the mix of which i think the LOD calculation is just factoring the month.

• ###### 10. Re: LEVEL OF DETAIL

Hi Segun,

Chris may come up with a different solution but I've got something that works. By adding those filters, the LOD calculation was just factoring the month out of context and ignoring the correct partitioning of your data.

To make this to work you need to specify the dimensions you want to be considered in calculating both % Spend and % Continuity.

Replace [Calculation1] with this code;

IF {FIXED [Type],[L4 PCAT Category Desc],[Year],[Country],[Brand Pos Name],[Principle],[Campaign Name],[Month] :

SUM([Actual Spend])} / {FIXED [Type],[L4 PCAT Category Desc],[Year],[Country],[Brand Pos Name],[Principle],[Campaign Name]: SUM([Actual Spend])}

> {FIXED [Type],[L4 PCAT Category Desc],[Year],[Country],[Brand Pos Name],[Principle],[Campaign Name],[Month] : AVG([%Spend])} THEN 1 ELSE 0 END

See attached workbook. Hope this helps.

Ossai

2 of 2 people found this helpful
• ###### 11. Re: LEVEL OF DETAIL

Thanks Okechuckwu, this has really helped.

Are you based in London?

• ###### 12. Re: LEVEL OF DETAIL

You're welcome Segun. Glad it helped.

Yes, I am based in London.

Ossai

• ###### 13. Re: LEVEL OF DETAIL

Apologies Okechukwu - I am just getting to grips with using this platform.