-
1. Re: Calculated Field
Joe OppeltApr 10, 2018 3:39 PM (in response to Anvesh Chinthala)
Depends on what you really want to do here.
In the attached I changed your CNT() to CNTD() and now you get 1 in every row. But the grand total is finding 27 distinct row numbers overall.
Do you have to see a 1 in every row for some reason? I created a FIXED LOD calc that tells you how many unique line numbers an order has, I displayed it on the sheet. You get a proper value of 57 in the grand total.
As for the Case Expression, for Order ID 60012290 you get 11 for the value because there are 11 rows of data for SLVR under line number 82. I changed the SUM to AVG and I think that's what you want to do there.
See attached.-
Workbook A.twbx 76.1 KB
-
-
2. Re: Calculated Field
Anvesh Chinthala Apr 11, 2018 9:51 AM (in response to Joe Oppelt)Hello Joe,
I like to see 1 in every row only if you could please.
Grand total is good its 57, thanks for the formula
This helps me a little bit but the problem is when put the fields 'Revision and 'lines per order' in bubble chart the numbers aren't accurate.
Total for Gold should be 3, Slvr is 4 and Brnz is 1 and the total null values should be 49 after eliminating nulls which are attached to gold and slvr.
I am attached an updated workbook please see the bubble chart in it.
Thanks for your help!
-
Updated - Workbook A.twbx 107.7 KB
-
-
3. Re: Calculated Field
Joe OppeltApr 11, 2018 10:35 AM (in response to Anvesh Chinthala)
I created a new calc called [Lines per order/line#]. This does the counting at the line number level, not at the OrderID level. I inserted it in Sheet 1, and added it to TEXT on the bubble chart.
-
Updated - Workbook B.twbx 102.3 KB
-
-
4. Re: Calculated Field
Anvesh Chinthala Apr 11, 2018 12:14 PM (in response to Joe Oppelt)Joe,
This looks great ! but one last thing, in bubble chart the blue bubble is the total should be 49 but it showing as 54.
In below Image, for Line number 82 their is Null and Slvr and I like to have zero for null and 1 for Slvr and same thing with Gold. So this gives me total null value as 49.
Out of 54, there are 5 nulls need to be zero so it gives the result as 49.
Thank you very much for your help.
-
5. Re: Calculated Field
Joe OppeltApr 11, 2018 1:41 PM (in response to Anvesh Chinthala)
This is why I asked about having a on every row. You're going to get a count for all the NULL rows if there is a 1 on it.
I modified [Lines per order/line#] to put a zero on the NULL when you have more than 1 revision. But now you have zeros on Sheet 1. If you want it both ways, make two calcs. Use one on Sheet 1 and the other on Bubble.
To tell if we have more than one revision, I made a calc called [Revisions per line]. (You'll notice that I had to insert a space in the calc if the value was null because tableau doesn't count nulls when it does COUNT and COUNTD.) then I used that calc in my change to [Lines per order/line#].
See attached.-
Updated - Workbook C.twbx 100.6 KB
-
-
6. Re: Calculated Field
Anvesh Chinthala Apr 11, 2018 3:13 PM (in response to Joe Oppelt)Perfect!
Thanks a lot Joe.
I have workbook with same data which has blanks instead of Nulls, do you the same formula can be applied?? or do I need to change anything in formula?
Thanks again
-
7. Re: Calculated Field
Joe OppeltApr 11, 2018 3:25 PM (in response to Anvesh Chinthala)
1 of 1 people found this helpfulNull and blank are different values and behave differently. All the stuff I was doing in there checking for ISNULL would not work on a blank. But in places where I am checking for NULL, you would need to check for " ". Note that " " is a space, whereas "" is a null. Play with the calcs step by step to make sure you are handling things correctly. You can see that in Sheet 1 I put my intermediate calcs onto the sheet so that I could see how things were happening under various conditions. Do that when you implement this in your actual data. Just play with one calc at a time until you are confident that it's behaving the way you want it to. Then move onto the next calc.
-
8. Re: Calculated Field
Anvesh Chinthala Apr 11, 2018 4:11 PM (in response to Joe Oppelt)Replaced Blank as Null and applied the formula it worked
Thanks for all your help, Joe