13 Replies Latest reply on Apr 25, 2018 5:04 PM by Zhouyi Zhang

# I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

COUNTD( DATEPART('day',[Audit Timestamp])) WHEN([% of Occupancy (for pie chart)]) >= .90 ELSE Null END))

is what i came up with but im getting a syntax error

• ###### 1. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

Hi, Jordan

It should be like this in tableau

Countd(

if [% of Occupancy (for pie chart)] >= .90 Then

DATEPART('day',[Audit Timestamp])

End

)

ZZ

• ###### 2. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

I think you're close...try something like this...

countd(

if [% of Occupancy (for pie chart)] >= .90 then datepart('day',[Audit Timestamp]) end

)

You don't need the "ELSE null"...that will happen automatically.

• ###### 3. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

I get "cannot mix aggregate and non-aggregate comparisons or results in 'if' expressions.

• ###### 4. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

I get "cannot mix aggregate and non-aggregate comparisons or results in 'if' expressions.

• ###### 5. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

this is the aggregate function.

• ###### 6. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

try putting ATTR() around the datepart function....

ATTR( datepart('day',[Audit Timestamp]))

• ###### 7. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

I get "Argument to COUNT (an aggregate function) is already an aggregation, and cannot be further aggregated.

• ###### 8. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

Its a bit difficult to completely diagnose what is going on without some more information (sample data, workbook, etc.), but I think a Level of Detail calculation fixed for whatever dimension you're evaluating the % occupancy should work.  e.g...

{FIXED <dimension with % occupancy calc> : countd ( if [% occupancy (for pie chart)] > 0.9 then datepart('day', [audit timestamp]) end )}

Note: you can't use ATTR() in LOD calcs nor table calculations.  I notice in your screenshot that your "Count of days over 90%" calc is a table calc...without more info its hard to understand why, but that may cause a limitation in terms of using LOD.  Alternatively, depending on the specifics, using LOD as an alternative to your table calc may prove to be a viable solution.

I hope that helps!

Cheers

Vinnie

1 of 1 people found this helpful
• ###### 9. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

I figured it out to a degree:

WINDOW_COUNT(if [% of Occupancy (for pie chart)] >= .90 Then COUNT(datepart('day',[Audit Timestamp])) end)

Now I just want the pie chart that is displaying metric for a given month to tel me how many times(days) in that month it was over 90% occupancy

• ###### 10. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

I figured it out to a degree:

WINDOW_COUNT(if [% of Occupancy (for pie chart)] >= .90 Then COUNT(datepart('day',[Audit Timestamp])) end)

Now I just want the pie chart that is displaying metric for a given month to tel me how many times(days) in that month it was over 90% occupancy

Can anyone assist me further?!!!

• ###### 11. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

I figured it out to a degree:

WINDOW_COUNT(if [% of Occupancy (for pie chart)] >= .90 Then COUNT(datepart('day',[Audit Timestamp])) end)

Now I just want the pie chart that is displaying metric for a given month to tel me how many times(days) in that month it was over 90% occupancy

• ###### 12. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

I figured it out to a degree:

WINDOW_COUNT(if [% of Occupancy (for pie chart)] >= .90 Then COUNT(datepart('day',[Audit Timestamp])) end)

Now I just want the pie chart that is displaying metric for a given month to tel me how many times(days) in that month it was over 90% occupancy

• ###### 13. Re: I want to create a calculation that counts the Total days of [audit timestamp] over 90% occupancy

Hi, Jordan

When you introduced window calculation, it is better to have a sample workbook with dummy data so that it will be easier to look into it as window calculation is related to dimensions and detail levels.

can you provide a sample workbook?

ZZ