-
1. Re: Calculated Field to capture another row's value
Daniel Vincent Feb 10, 2016 3:08 PM (in response to Cynthia Wang) -
2. Re: Calculated Field to capture another row's value
pooja.gandhi Feb 10, 2016 3:17 PM (in response to Daniel Vincent)Hi Daniel!
Good to see out and about in the community again!
I have a feeling that {FIXED [Id No]:MIN([Code])} will result in a MIN(code) alphabetically and not literally the 1st occurence. So if LS corresponds to month 4 and OP corresponds to month 1 then your formula would still yield LS not OP. I don't have access to Tableau right now so I can't really test anything. But just by looking at it, feel like this wouldn't work.
-
3. Re: Calculated Field to capture another row's value
Daniel Vincent Feb 10, 2016 4:02 PM (in response to pooja.gandhi)Thank you for keeping me honest Pooja...and good to be back
I was trying to do round hole/square peg and make LoD work. May need to play with it but this will work assuming you don't find something else I missed or can write it more eloquently than I.
You will need to write the following table calc's:
WINDOW_MIN(IF NOT ISNULL(ATTR([Code])) THEN INDEX() END) -- This one will give us the first instance of a non null
LOOKUP(ATTR([Code]),[c.calc2]-INDEX()) -- This will lookup up the code from the above calc
You'll need to edit the last calc when you put it on the text shelf:
You'll need to go into advanced for both calc's then set addressing to id, month, code and finally restarting at id:
-
4. Re: Calculated Field to capture another row's value
Alexander Mou Feb 10, 2016 3:59 PM (in response to pooja.gandhi)Check out this to see if it works for you
Use Tableau Public to open it.
Created two calc fields, both compute using Month:
[Min Month]
WINDOW_MIN( if Not ISNULL( ATTR( [Code] ) )then ATTR( [Month] ) END)
[Month_Code]
WINDOW_MAX(if [Min Month] =ATTR([Month]) then ATTR( [Code] )END)
-
5. Re: Calculated Field to capture another row's value
pooja.gandhi Feb 10, 2016 4:07 PM (in response to Daniel Vincent)haha that explanation turned out to be longer than expected
-
6. Re: Calculated Field to capture another row's value
pooja.gandhi Feb 10, 2016 4:16 PM (in response to Daniel Vincent)How about this?
IF [Month] = IF NOT isnull([Code]) then { fixed [ID_no] : min([Month]) } END then [Code] end
-
7. Re: Calculated Field to capture another row's value
Daniel Vincent Feb 10, 2016 4:21 PM (in response to pooja.gandhi) -
8. Re: Calculated Field to capture another row's value
Alexander Mou Feb 10, 2016 4:28 PM (in response to Daniel Vincent)Put 2 solutions into this viz
One with LOD, the other without.
-
9. Re: Calculated Field to capture another row's value
pooja.gandhi Feb 10, 2016 4:30 PM (in response to Daniel Vincent)hmm thanks for testing that. I feel like table calc is such an overkill for a simple ask. Maybe something like this?
IF [Month] = {FIXED [ID no] : MIN(IF NOT ISNULL([Code]) then [Month] END) } THEN [Code] END
If not, I will fiddle with it tomorrow. But I do feel like, LOD would be the way to go!
-
10. Re: Calculated Field to capture another row's value
Daniel Vincent Feb 10, 2016 4:43 PM (in response to pooja.gandhi)Looks like table calc is unavoidable as Alexander Mou was able to do it using both LoD/table calc.
I don't see a benefit of one over the other so it's really dealers choice IMHO.
-
11. Re: Calculated Field to capture another row's value
Steve Mayer Feb 10, 2016 4:53 PM (in response to Cynthia Wang)Cynthia (and all involved)... I have an LOD solution that works for your use case. Just a little tricky because it involves nesting LOD calcs, To keep myself sane, I created the calculations in two steps.
EDIT: Hadn't seen Alexander's solution, which uses LOD/table calculation hybrid - also a viable solution
Step 1
Create an LOD calculation [First Month] that returns the First Month with a non-NULL Code for any ID. The formula is:
{ FIXED [ID no] : MIN(IF NOT ISNULL([Code]) THEN [Month] END) }
Step 2
Create a 2nd LOD calculation [First Code] that returns the Code of the First Month. The formula:
{ FIXED [ID no] : MIN(IIF([Month] = [First Month], [Code], NULL)) }
Note that MIN could also be MAX, assuming there is only one non-NULL Code for any given ID no and Month. It is just returning the Code for the month of the first LOD calculation [First Month].
Step 3
Verify the results.
Workbook attached,
-Steve
-
First Code.twbx 14.9 KB
-
-
12. Re: Calculated Field to capture another row's value
Alexander Mou Feb 10, 2016 5:12 PM (in response to Steve Mayer)Steve,
Good trick of using LOD to replace the window function in the second step!
It does the same thing.
-
13. Re: Calculated Field to capture another row's value
Cynthia Wang Feb 11, 2016 8:02 AM (in response to Steve Mayer)Thanks Steve! This is exactly what I want. Smart to make it nested. I had similar idea and was trying to look for LOD calculations that work for dimension but I didn't realize that MIN or MAX can work when there's NULL value.
Daniel and Alex, thanks for the help! I haven't used WINDOWS_ calculation before, but since I was trying to create a calculated field for future filtering/category instead of a calculation inside the table, so I assume table calculation won't work. Is that correct?
Cynthia
-
14. Re: Calculated Field to capture another row's value
Alexander Mou Feb 11, 2016 8:17 AM (in response to Cynthia Wang)Table calc filters work fine.
Depends on what you want to.
On Thursday, February 11, 2016, Cynthia Wang <tableaucommunity@tableau.com>