-
1. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter
swaroop.gantela Jun 29, 2016 4:52 PM (in response to Ben Rasilim)Ben,
I think you might be able to use the third suggestion on this page:Add a Calculated Field to the Filter Shelf | Tableau Software
If your comparison is Calculation1, then you would turn it into a Table Calculation using:
LOOKUP([Calculation1],0)
I think this new table calculation can then be used a filter and as a dimension.
-
210062Case Problem 1.twbx 40.1 KB
-
-
2. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter
Ben Rasilim Jun 29, 2016 11:19 PM (in response to swaroop.gantela)1 of 1 people found this helpfulHi Swaroop,
Thank you for your response. I don't know why I haven't come across that page. This seems to solve my problem.
I will let you know if I come into another problem.
Again, thank your for your assistance.
Best Regards
Ben
-
3. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter
Ben Rasilim Jun 29, 2016 11:19 PM (in response to swaroop.gantela)Hi Swaroop,
Something seems to be still missing.
This doesn't allow me to flag each record as either 'class1' or 'regular', it seems to only flag what is in the workbook
when I tried to filter when the workbook is empty it only shows the option 'regular' as shown below.
is there a way I can actually flag every record row by row in the source data (tableau extract)
an actual calculated field of vlookup to a reference table.
Please help.
Thanks and regards
Ben
-
5. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter
Ben Rasilim Jun 30, 2016 11:09 PM (in response to swaroop.gantela)Swaroop,
I really appreciate you helping me. below is roughly what I had in mind for the report. there are actually another field called store which is one hierarchy above item and there are more classifications. then I only want to filter and show the class without the regular one.
The tran Id will only shows the distinct count of Tran Ids that classified as 'class1' / 'class2'
so basically show how many transactions are in each class. is it possible to do that?
Please advise
Thanks and regards
Ben
-
6. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter
swaroop.gantela Jul 1, 2016 7:48 PM (in response to Ben Rasilim)Ben,
Please see if the attached is closer to the requirement.
There are very likely easier, more straightforward ways to do this,
buy maybe this can give ideas.
I don't think the LookUp calculation is needed after all.
Mostly what seems to be needed is Window calculations.
The classification is as you described:
IF ATTR([Value1])>ATTR([Sheet1 (210062ref)].[Limit]) THEN "class1"
ELSE "regular"
END
Separate Counts and ValueTotals are made for Class1 and for the Total (including Regular).
For Count of Trans in Class1 :
WINDOW_COUNT(COUNT([Tran ID]))
For Value of Class1:
IF [classfication]="class1" THEN SUM([Value1]) END
For the Total including Regular, I used Level of Detail Calculations:
Count:
{ FIXED [Store]:COUNT([Tran ID])}
Value:
{ FIXED [Store]:SUM([Value1])}
Now, I put the [Classification] on the Columns shelf.
Of note, I right-clicked on its pill and selected to
"Ignore in Table Calculations"
Then made a calculated field to tell which Count to show:
IF FIRST()=0
THEN IF [classfication]="class1" THEN [CountClass]
ELSEIF [classfication]="regular" THEN SUM([CountTotal])
END
END
(Because Tran ID is on the detail shelf, this creates many copies of the count/value,
so the FIRST() was needed)
Likewise for value:
IF FIRST()=0
THEN IF [classfication]="class1" THEN WINDOW_SUM([ValueClass])
ELSEIF [classfication]="regular" THEN SUM([ValueTotal])
END
END
-
210062Case Problem 3.twbx 66.1 KB
-
-
7. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter
Ben Rasilim Jul 4, 2016 12:34 AM (in response to swaroop.gantela)Swaroop,
it took me a while to understand all the formula and recreate your report on the same data.
from what I understand, this basically put all tran id into the worksheet but only shows the calculation along tables.
I haven't tried it on my real data, but I'm not sure that I can do it this way, since my real data is 36 million rows with at least 10 millions unique tran id. I'm not sure that I will be able to run the report without crashing. since it's one of my first problem handling this data.
Is there a way for me to do it without having to put the tran id into the worksheet?
Thank you for your time and patience
Regards
Ben
-
8. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter
swaroop.gantela Jul 5, 2016 7:32 AM (in response to Ben Rasilim)Ben,
Maybe we should revisit the separation of the sheets, which appears to be the source of the need for a workaround.
Level of Detail calculations may have had some benefit in simplifying things, but I think are hindered by the separation problem too.
With the classification, is that something that is changing and being updated at some time interval in the separate sheet?
How complicated of a calculation would it be, or is it mostly comparing against constants?
If it's not too bad, maybe best to create a calculation that could do the classification just off of the data sheet.
Or maybe some parameters could be created that the user would set to the needed values?
If these options are not feasible, will think on it more.
I certainly invite the community for assistance.
-
9. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter
Ben Rasilim Jul 7, 2016 6:56 PM (in response to swaroop.gantela)Hi Swaroop,
Sorry it took me a while to get back to you.
The classification is not changing that much. I would say just additional item or minor change.
I did thought about creating a really long IF function with every item in the calculation. but with 30k number of items, I thought the processing will take forever every time I want to load a report. so it's not feasible
what I'm doing right now is to create a local sql server on my computer containing my data and the reference table. that way I can join the transaction data table with reference table, not just blending it.
I realize one thing that I really want on tableau is the ability to join table from multiple data extract. not just join table from 1 database. data blending is just not enough and a bit complicated to do complex blend.
Thanks for your time and assistance.
Ben
-
10. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter
swaroop.gantela Jul 19, 2016 2:01 PM (in response to Ben Rasilim)Ben,
Would this functionality in Tableau 10 Beta help?
Integrate Your Data with Cross-Database Joins in Tableau 10 | Tableau Software
-
11. Re: Creating Calculated field that compares values from 2 data source and I can use as a filter
Ben Rasilim Jul 27, 2016 1:53 AM (in response to swaroop.gantela)Hi Swaroop,
Yes, that looks like it would actually solve my problem. The ability to joins data from different database. Or if possible joins data between tableau extract and a database.
I look forward when I can use the feature. Do you happen to know how I can try the Tableau 10 Beta?
Sorry it took a while to reply, I was occupied with some other stuff.
Thanks for the info
Best Regards
Ben