13 Replies Latest reply on Oct 19, 2016 1:23 AM by Raju Kumar

Calculated Fiedl

Hello Tableau World,

I'm trying to create a calculation but having some issues. I'm working with attendance data by fiscal week by region. I have only three fields to create this calculated field.

Region - ABU, APJ and EMEA

Registration Status - Cancelled, Cancelled (No Show) and Registered

Person Person ID - unique badge numbers for students

The calculation should consist of taking the number of "registered" students by the grand total of students for registration status. For example, the ABU attendance total for fiscal week 1 was 61 = Cancelled, 19 = Cancelled (no show) and 342 = registered for a grand total of 422. the way we calculate this would be 342/422 = 81%. The registration status in populated by taking a count of Person Person ID.

Any input how to create this calculated field?

John

• 1. Re: Calculated Fiedl

If I'm following correctly, you want something like this:

COUNTD([Person Id]) / {FIXED [WeekId] : COUNTD([Person Id])}

The {} counts across the current week. The first COUNTD() will count at whatever grain the rest of the view is at, so you'd want to break it out by status.

• 2. Re: Calculated Fiedl

SUM(if [Registration Status] == 'Registered' then 1 end)/{FIXED [Week ID]: SUM([Number of Records])}

Edit: oops I missed the 'Weeks' part... I have edited my calculation to reflect that knowledge.

• 3. Re: Calculated Fiedl

thank you Matt and Derrick, i tried both and it doesn't work. it says i can't mix aggregate and non aggregate functions

• 4. Re: Calculated Fiedl

Oh, yah - that's right. Wrap it in a MAX():

COUNTD([Person Id]) / MAX({FIXED [WeekId] : COUNTD([Person Id])})

• 5. Re: Calculated Fiedl

should i put in the calculation count person id only for registered divided by total of all person id for fiscal week?

• 6. Re: Calculated Fiedl

Yah, you could do something like that in the COUNTD() function:

COUNTD(IF [Status] ='Registered' THEN [Person Id] END) / MAX({FIXED [WeekId] : COUNTD([Person Id])})

• 7. Re: Calculated Fiedl

With Derrick's original answer, you can also right-click on the 'Cancelled' parts on the Status axis and click 'Hide', instead of using the IF statement.

• 8. Re: Calculated Fiedl

i have the calculation like this:

COUNTD(if [Registration Status]= 'Registered' then [Person Person No]end/MAX({fixed[FW]:COUNTD([Person Person No])})

i'm getting this error message:

expected closing parenthesis or comma while parsing argument list for COUNTD

• 9. Re: Calculated Fiedl

You're missing a closing parenthesis after your "end" (Before the /)

• 10. Re: Calculated Fiedl

thanks so much gentlemen.

Derrick that fixed the problem but the calculation isn't adding up correctly.

matt that worked.

• 11. Re: Calculated Fiedl

It works perfectly with the hiding? Do you need any more help?

• 12. Re: Calculated Fiedl

it works with hiding but i want the calculated field so i can create variances between fiscal weeks. any help would be greatly appreciated.

• 13. Re: Calculated Fiedl

Hi Jonathan,

Can you try with the below.

(IF attr([Registration Status])="Registered" THEN COUNTD([Person ID]) END)/sum({FIXED [Fiscal week]:COUNTD([Person ID])})

Regards,

Raju