5 Replies Latest reply on Nov 9, 2016 11:25 PM by xinyi.ng

# How to derive variable that gives first value for every category

Hi

How do i derive a variable (value2) such that it will give the first value for every group?

 group value value2 A 1 1 A 1 0 A 1 0 B 0 0 B 0 0 C 1 1 C 1 0 C 1 0

I tried this but it didnt work:

{ FIXED [group]: iif(FIRST()=0,value,0)}

It gave me the aggregate non-aggregate error.

• ###### 1. Re: How to derive variable that gives first value for every category

Hi Xinyi,

I might be missing something, but not sure you need a LOD for this (if that's the only thing you want in the view).

Try just using a table calc like:

iif(FIRST()=0,min([Value]),0)

and then use right click / edit table calc / compute using and select specific dimensions / "value".

Or the pane defined by the "group".

1 of 1 people found this helpful
• ###### 2. Re: How to derive variable that gives first value for every category

Hi Xinyi,

Please see the excel data below. You will be required to add the sr no to make each row distinct else it will aggregate the output by group.

Please refer to the screenshot below and arrange the dimensions and measure as per shown in the pictures. I have also shown what table calculation that I have used. along with the value2 field formula. I am attaching the tableau 10.1 version of the workbook since I am using latest version.

Thanks and Regards,

Ashish Chaudhari

• ###### 3. Re: How to derive variable that gives first value for every category

Hi Nicco,

I think that's sounds perfect. Let me give it a try to it.

-Ashish

• ###### 4. Re: How to derive variable that gives first value for every category

You will have to add the serial number to it in the data else you will get the aggregated value by the Group. I have tried your formula and it works.

See what happens when I remove the serial number.

-Ashish

• ###### 5. Re: How to derive variable that gives first value for every category

Hi

Thanks both. What should I do if I want to derive a variable permanently in the table, so I can avoid using the table calculation (if I'm not wrong this calculate the variable dynamically) depending on what I put in the row/column? For example I want to plot a graph which is the sum of all value2, I would be dropping group/sr_no in the row or column.