-
1. Re: Line Chart with more than one time dimension
Jim DehnerNov 28, 2017 8:41 AM (in response to Matteo Andolfi)
1 of 1 people found this helpfulHi
I think the issue is with your if statement logic - your If statements check to values that are "This Month" or "last Month" based on a parameters
The work with the 1 month scenario because a data is either in this month - last month or it is out of range
When you try to extend to 2 months you have dates that you want to see in the current month interval and in the last month interval - but that can't happen - when the record is processed it returns a True when checked against the current interval and the process moves on to the next record -
The record can not return a true against 2 separate clauses in the statement
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
-
2. Re: Line Chart with more than one time dimension
Matteo Andolfi Nov 28, 2017 8:46 AM (in response to Jim Dehner)Hi Jim and thanks for your reply.
Yes, the problem is the if statement, but how i can achieve my target?
do you know?
thanks again
Matteo
-
3. Re: Line Chart with more than one time dimension
Jim DehnerNov 28, 2017 8:50 AM (in response to Matteo Andolfi)
Please attach your workbook in a TWBX format
Jim
-
4. Re: Line Chart with more than one time dimension
Tyler Garrett Jun 20, 2019 8:04 AM (in response to Matteo Andolfi)Are you familiar with a DIM_TIME table, from a EDW perspective, it's a best practice - as each dimensional or agg table will need a logical time table.
Especially a best practice if you have multiple dates that are needing to be 'overlapped'...
PART 1:
If the dates are not different
Then JOIN on date... Date=Date... pretty much always and forever, which makes SQL so easy
If you need to join on DATE, you will need to aggregate to a DATE level prior to joining in Tableau. Oh snap.
SQL:
Select
dim_stuff, measure, measure, dim_morestuff, DATE
from table
GROUP BY Day(date), dim_stuff, dim_morestuff
not the most THE BEST sql code, but is code that is necessary to join your date on date, so I'm keeping it really basic.
If the dates are different
For big companies, they have multiple fiscal flags - based on their clients, or customer needs... Things get more complex and keys are necessary, so I'm going to keep it high level.
Multiple dates, that are subtly different, enables you to generate this DIM_Time table, wish EASE -> if you like excel and have a DBA
Each time has a means of syncing, no matter how it hits your data, everything can be synced in a table.
If you have two times, that need to be synced on ONE table. Then I would recommend simulating it first inside of EXCEL, then running your report off this sample data.
I like to draw this on a whiteboard - and I'm afraid we won't have that luxury, so I will go ahead and illustrate it on excel.
No matter the date, or the complexity, a simple table to explain your 'needs' - will offer your data architect or DBA - a simple explanation of your needs.
Also, you can materialize a TABLE, and forecast it out to 2020, and dump it to CSV, and import the CSV into your database, call it DIM_TIME, wahoo!
I would strongly advise against 'ETLing' your data on the front end, or expect super slow workbook in your future.
From a date perspective, this will always be a very frustrating/challenging process to 'master' in the product (and I've only met one person in my life that could come up with 'anything you want' on the front end), and I'm saying this with a backbone of data warehousing for companies like Goodwill, which had stores in different timezones, and different flags for different dates. And I feel strongly about mastering your dates in a relational table. And then depending on your needs, you can pull from the master DIM_TIME table.
Again, I've played this DATE judo game my entire business intelligence career - and if this isn't a nail on your solution, no worries, people search these 'phrases' - in the thousands per MONTH. And I'm looking to answer future searches on the forums/google, etc.
Just ignore this, I'm merely showing you that I've played some really wild Date games, and cleaning it up on the backend is much easier than playing this front-end judo... (hopefully this isn't the solution, but I've done 100's and 100's of whiteboard sessions working out awkward date things)
If anything, I highly recommend you whiteboard it, and I promise the solution will pop out faster.
Here's another example of figuring out wild date stuff.
Each time I hear 'different dates' and 'reporting' ---> I always ask for mapping documentation --> for the datebase.
To determine if there is already a DIM_TIME table generated from 'previous solutions' and who knows - this might be done already. And that's great.
I know I'm speaking about this very high level, but that's as good as it gets when I don't have read access to your internal database. And throwing anything at you that isn't related to ETL prior to, may only be a wheel spin for you, unless you're an absolute JEDI in Tableau. Which *hats off and kowtow to you if you are.
Best,
Tyler
Dev3lop
-
5. Re: Line Chart with more than one time dimension
Tyler Garrett Jun 20, 2019 8:05 AM (in response to Matteo Andolfi)If you're doing a period over period (look at calc below). This would be the easiest path to where you're driving.
And I've never heard anyone say SQL wasn't possible - unless they were a consultant and their client doesn't trust them running sql in their environment.
That may be a bad deal because now the client is waiting on you to do something that can be easily fulfilled in SQL.
Which from my perspective, is burning billable hours unnecessarily.
You may want to bubble up to them that you need to do SQL to complete the request - without too much complex front end solutions, which won't be supportable by the customer unless you write verbose documentation around 'why and how.'
There's a double edge blade here you're playing with, and it's the value add of a front end 'chunk of code' to make something easy to do - in SQL... And no, there's not DATA too big for SQL, that's not a thing mate. I would highly recommend SQL - or fdfghjkljhgfdghjkl;kjhgfdghjkl;kjhcg LOL
Best,
Tyler
Dev3lop
-
6. Re: Line Chart with more than one time dimension
Tyler Garrett Jun 20, 2019 8:05 AM (in response to Matteo Andolfi)Last but not least. You can do multiple lines per DATE, by simply SQLing measures into dimensional stacks. Again, SQL is the only path for this one. Especially if you're throwing in stacked bar charts with multiple lines, which is a BIG idea request - but a simple SQL solution.
No data is too big FOR SQL, unless you're talking about IoT devices, which require instant read/write and that's not what we are talking about
Here's a picture of some practice code I built this year, that shows how I stacked multiple lines, and stacked bars because the request was BEYOND Tableau Native features. And SQL ... IS ... mandatory. There's no alternative, except +1 the IDEA Forum.
Best,
Tyler
Dev3lop
-
7. Re: Line Chart with more than one time dimension
Matteo Andolfi Nov 29, 2017 1:20 AM (in response to Matteo Andolfi)Hi,
Thank you Tyler for all the explanation....
unfortunatelly my datasource is not a relational DB, it is Cloudera Hive (I forgot to mention...).
With this datasource, I cannot perform joins or other operations because there are 134 millions of records and the time that i've have to wait for the result is huge.
And also, the operation on date with Hive is not so simple, i have to use DATETIME to parse the date and RAWSQL to get the various date (i.e: date-1month, date-1year....)
I cannot attach directly a workbook because my customer's Tableau , is not licensed to make twbx
by the way i attach a simple workbook created with the trial version of tableau 10.4
I really appreciate your help!
Thanks!
-
Book2.twbx 1.0 MB
-
-
8. Re: Line Chart with more than one time dimension
Matteo Andolfi Nov 29, 2017 8:44 AM (in response to Matteo Andolfi)Hi all,
I've some news:
I've Used The Data Blending functionality, 3 times on the same dataset, (LastWeek, LastMonth, LastYear)
Like this post: Re: Help needed with Date calculations
One thing that i don't like it is the following:
The query made by Tableau when DateFrom=2016-01-01 and DateTo=2017-08-31 is like:
[...]FROM `schema`.`table`
WHERE (((`table`.`filter1` = 'XXX')
AND ((`table`.`filter2` = 'XXX')
AND (`table`.`filter3` = 'XXX')))
AND (((`table`.`filter4` = 'XXX')
AND (`table`.`filter5` = 'XXX'))
AND ((`table`.`filter6` = 'XXX')
AND ( (CASE
WHEN (1 IS NULL)
OR (10 IS NULL) THEN
NULL
WHEN 10 < 1 THEN
''
WHEN 1 < 1 THEN
SUBSTRING(CAST((CAST(`table`.`day` AS TIMESTAMP) + interval 1 month) AS STRING),CAST(1 AS INT),CAST(10 AS INT))
ELSE SUBSTRING(CAST((CAST(`table`.`day` AS TIMESTAMP) + interval 1 month) AS STRING),CAST(1 AS INT),CAST(10 AS INT)) END) IN ('2016-01-01', '2016-01-02', '2016-01-03',
'2016-01-04', '2016-01-05', '2016-01-06', '2016-01-07', '2016-01-08', '2016-01-09', '2016-01-10', '2016-01-11', '2016-01-12', '2016-01-13', '2016-01-14', '2016-01-15', '2016-01-16',
'2016-01-17', '2016-01-18', '2016-01-19', '2016-01-20', '2016-01-21', '2016-01-22', '2016-01-23', '2016-01-24', '2016-01-25', '2016-01-26', '2016-01-27', '2016-01-28', '2016-01-29',
'2016-01-30', '2016-01-31', '2016-02-01', '2016-02-02', '2016-02-03', '2016-02-04', '2016-02-05', '2016-02-06', '2016-02-07', '2016-02-08', '2016-02-09', '2016-02-10', '2016-02-11',
'2016-02-12', '2016-02-13', '2016-02-14', '2016-02-15', '2016-02-16', '2016-02-17', '2016-02-18', '2016-02-19', '2016-02-20', '2016-02-21', '2016-02-22', '2016-02-23', '2016-02-24',
'2016-02-25', '2016-02-26', '2016-02-27', '2016-02-28', '2016-02-29', '2016-03-01', '2016-03-02', '2016-03-03', '2016-03-04', '2016-03-05', '2016-03-06', '2016-03-07', '2016-03-08',
'2016-03-09', '2016-03-10', '2016-03-11', '2016-03-12', '2016-03-13', '2016-03-14', '2016-03-15', '2016-03-16', '2016-03-17', '2016-03-18', '2016-03-19', '2016-03-20', '2016-03-21',
'2016-03-22', '2016-03-23', '2016-03-24', '2016-03-25', '2016-03-26', '2016-03-27', '2016-03-28', '2016-03-29', '2016-04-01', '2016-04-02', '2016-04-03', '2016-04-04', '2016-04-05',
'2016-04-06', '2016-04-07', '2016-04-08', '2016-04-09', '2016-04-10', '2016-04-11', '2016-04-12', '2016-04-13', '2016-04-14', '2016-04-15', '2016-04-16', '2016-04-17', '2016-04-18',
'2016-04-19', '2016-04-20', '2016-04-21', '2016-04-22', '2016-04-23', '2016-04-24', '2016-04-25', '2016-04-26', '2016-04-27', '2016-04-28', '2016-04-29', '2016-04-30', '2016-05-01',
'2016-05-02', '2016-05-03', '2016-05-04', '2016-05-05', '2016-05-06', '2016-05-07', '2016-05-08', '2016-05-09', '2016-05-10', '2016-05-11', '2016-05-12', '2016-05-13', '2016-05-14',
'2016-05-15', '2016-05-16', '2016-05-17', '2016-05-18', '2016-05-19', '2016-05-20', '2016-05-21', '2016-05-22', '2016-05-23', '2016-05-24', '2016-05-25', '2016-05-26', '2016-05-27',
'2016-05-28', '2016-05-29', '2016-05-30', '2016-06-01', '2016-06-02', '2016-06-03', '2016-06-04', '2016-06-05', '2016-06-06', '2016-06-07', '2016-06-08', '2016-06-09', '2016-06-10',
'2016-06-11', '2016-06-12', '2016-06-13', '2016-06-14', '2016-06-15', '2016-06-16', '2016-06-17', '2016-06-18', '2016-06-19', '2016-06-20', '2016-06-21', '2016-06-22', '2016-06-23',
'2016-06-24', '2016-06-25', '2016-06-26', '2016-06-27', '2016-06-28', '2016-06-29', '2016-06-30', '2016-07-01', '2016-07-02', '2016-07-03', '2016-07-04', '2016-07-05', '2016-07-06',
'2016-07-07', '2016-07-08', '2016-07-09', '2016-07-10', '2016-07-11', '2016-07-12', '2016-07-13', '2016-07-14', '2016-07-15', '2016-07-16', '2016-07-17', '2016-07-18', '2016-07-19',
'2016-07-20', '2016-07-21', '2016-07-22', '2016-07-23', '2016-07-24', '2016-07-25', '2016-07-26', '2016-07-27', '2016-07-28', '2016-07-29', '2016-07-30', '2016-08-01', '2016-08-02',
'2016-08-03', '2016-08-04', '2016-08-05', '2016-08-06', '2016-08-07', '2016-08-08', '2016-08-09', '2016-08-10', '2016-08-11', '2016-08-12', '2016-08-13', '2016-08-14', '2016-08-15',
'2016-08-16', '2016-08-17', '2016-08-18', '2016-08-19', '2016-08-20', '2016-08-21', '2016-08-22', '2016-08-23', '2016-08-24', '2016-08-25', '2016-08-26', '2016-08-27', '2016-08-28',
'2016-08-29', '2016-08-30', '2016-08-31', '2016-09-01', '2016-09-02', '2016-09-03', '2016-09-04', '2016-09-05', '2016-09-06', '2016-09-07', '2016-09-08', '2016-09-09', '2016-09-10',
'2016-09-11', '2016-09-12', '2016-09-13', '2016-09-14', '2016-09-15', '2016-09-16', '2016-09-17', '2016-09-18', '2016-09-19', '2016-09-20', '2016-09-21', '2016-09-22', '2016-09-23',
'2016-09-24', '2016-09-25', '2016-09-26', '2016-09-27', '2016-09-28', '2016-09-29', '2016-09-30', '2016-10-01', '2016-10-02', '2016-10-03', '2016-10-04', '2016-10-05', '2016-10-06',
'2016-10-07', '2016-10-08', '2016-10-09', '2016-10-10', '2016-10-11', '2016-10-12', '2016-10-13', '2016-10-14', '2016-10-15', '2016-10-16', '2016-10-17', '2016-10-18', '2016-10-19',
'2016-10-20', '2016-10-21', '2016-10-22', '2016-10-23', '2016-10-24', '2016-10-25', '2016-10-26', '2016-10-27', '2016-10-28', '2016-10-29', '2016-10-30', '2016-11-01', '2016-11-02',
'2016-11-03', '2016-11-04', '2016-11-05', '2016-11-06', '2016-11-07', '2016-11-08', '2016-11-09', '2016-11-10', '2016-11-11', '2016-11-12', '2016-11-13', '2016-11-14', '2016-11-15',
'2016-11-16', '2016-11-17', '2016-11-18', '2016-11-19', '2016-11-20', '2016-11-21', '2016-11-22', '2016-11-23', '2016-11-24', '2016-11-25', '2016-11-26', '2016-11-27', '2016-11-28',
'2016-11-29', '2016-11-30', '2016-12-01', '2016-12-02', '2016-12-03', '2016-12-04', '2016-12-05', '2016-12-06', '2016-12-07', '2016-12-08', '2016-12-09', '2016-12-10', '2016-12-11',
'2016-12-12', '2016-12-13', '2016-12-14', '2016-12-15', '2016-12-16', '2016-12-17', '2016-12-18', '2016-12-19', '2016-12-20', '2016-12-21', '2016-12-22', '2016-12-23', '2016-12-24',
'2016-12-25', '2016-12-26', '2016-12-27', '2016-12-28', '2016-12-29', '2016-12-30', '2017-01-01', '2017-01-02', '2017-01-03', '2017-01-04', '2017-01-05', '2017-01-06', '2017-01-07',
'2017-01-08', '2017-01-09', '2017-01-10', '2017-01-11', '2017-01-12', '2017-01-13', '2017-01-14', '2017-01-15', '2017-01-16', '2017-01-17', '2017-01-18', '2017-01-19', '2017-01-20',
'2017-01-21', '2017-01-22', '2017-01-23', '2017-01-24', '2017-01-25', '2017-01-26', '2017-01-27', '2017-01-28', '2017-01-29', '2017-01-30', '2017-01-31', '2017-02-01', '2017-02-02',
'2017-02-03', '2017-02-04', '2017-02-05', '2017-02-06', '2017-02-07', '2017-02-08', '2017-02-09', '2017-02-10', '2017-02-11', '2017-02-12', '2017-02-13', '2017-02-14', '2017-02-15',
[...]
So many day! why Tableau doesn't use a BETWEEN? :O
Any helps?
Thanks in advance!
Matteo
-
9. Re: Line Chart with more than one time dimension
Andrej Schmelzer Nov 29, 2017 9:35 AM (in response to Matteo Andolfi)What is the exact requirement your customer has?
Your issue is that your If/Elseif statement can only return either "current" or "previous month". For more months then 1, there will be dataparts which have are both.
I am assuming that your customer is mostly interested in having the current sales/whatever as well as the difference in sales/whatever to the previous month.
This is likely a lot easier to do with a lookup table calc.
-
10. Re: Line Chart with more than one time dimension
Tyler Garrett Nov 29, 2017 10:49 AM (in response to Matteo Andolfi)Querying 134 million records doesn't take that long on an in memory database unless the joins are incorrect.
Good news. And bad news. Regrettably - You might be in over your head, contact your senior architect or principal architect (hopefully you're not freelance) - do you have a data architect to help you? Are you internal? You really need to bubble this up to the senior DBA or Hive admin.
Join's appear to be possible: here's a PASTE directly from the website.
JOIN is a clause that is used for combining specific fields from two tables by using values common to each one. It is used to combine records from two or more tables in the database. It is more or less similar to SQL JOIN.
Syntax
join_table: table_reference JOIN table_factor [join_condition] | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition | table_reference LEFT SEMI JOIN table_reference join_condition | table_reference CROSS JOIN table_reference [join_condition]
Example
We will use the following two tables in this chapter. Consider the following table named CUSTOMERS..
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
Consider another table ORDERS as follows:
+-----+---------------------+-------------+--------+ |OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 | 3 | 3000 | | 100 | 2009-10-08 00:00:00 | 3 | 1500 | | 101 | 2009-11-20 00:00:00 | 2 | 1560 | | 103 | 2008-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+
There are different types of joins given as follows:
- JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
JOIN
JOIN clause is used to combine and retrieve the records from multiple tables. JOIN is same as OUTER JOIN in SQL. A JOIN condition is to be raised using the primary keys and foreign keys of the tables.
The following query executes JOIN on the CUSTOMER and ORDER tables, and retrieves the records:
hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT FROM CUSTOMERS c JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);
On successful execution of the query, you get to see the following response:
+----+----------+-----+--------+ | ID | NAME | AGE | AMOUNT | +----+----------+-----+--------+ | 3 | kaushik | 23 | 3000 | | 3 | kaushik | 23 | 1500 | | 2 | Khilan | 25 | 1560 | | 4 | Chaitali | 25 | 2060 | +----+----------+-----+--------+
LEFT OUTER JOIN
The HiveQL LEFT OUTER JOIN returns all the rows from the left table, even if there are no matches in the right table. This means, if the ON clause matches 0 (zero) records in the right table, the JOIN still returns a row in the result, but with NULL in each column from the right table.
A LEFT JOIN returns all the values from the left table, plus the matched values from the right table, or NULL in case of no matching JOIN predicate.
The following query demonstrates LEFT OUTER JOIN between CUSTOMER and ORDER tables:
hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE FROM CUSTOMERS c LEFT OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);
On successful execution of the query, you get to see the following response:
+----+----------+--------+---------------------+ | ID | NAME | AMOUNT | DATE | +----+----------+--------+---------------------+ | 1 | Ramesh | NULL | NULL | | 2 | Khilan | 1560 | 2009-11-20 00:00:00 | | 3 | kaushik | 3000 | 2009-10-08 00:00:00 | | 3 | kaushik | 1500 | 2009-10-08 00:00:00 | | 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | | 5 | Hardik | NULL | NULL | | 6 | Komal | NULL | NULL | | 7 | Muffy | NULL | NULL | +----+----------+--------+---------------------+
RIGHT OUTER JOIN
The HiveQL RIGHT OUTER JOIN returns all the rows from the right table, even if there are no matches in the left table. If the ON clause matches 0 (zero) records in the left table, the JOIN still returns a row in the result, but with NULL in each column from the left table.
A RIGHT JOIN returns all the values from the right table, plus the matched values from the left table, or NULL in case of no matching join predicate.
The following query demonstrates RIGHT OUTER JOIN between the CUSTOMER and ORDER tables.
notranslate"> hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE FROM CUSTOMERS c RIGHT OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);
On successful execution of the query, you get to see the following response:
+------+----------+--------+---------------------+ | ID | NAME | AMOUNT | DATE | +------+----------+--------+---------------------+ | 3 | kaushik | 3000 | 2009-10-08 00:00:00 | | 3 | kaushik | 1500 | 2009-10-08 00:00:00 | | 2 | Khilan | 1560 | 2009-11-20 00:00:00 | | 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | +------+----------+--------+---------------------+
FULL OUTER JOIN
The HiveQL FULL OUTER JOIN combines the records of both the left and the right outer tables that fulfil the JOIN condition. The joined table contains either all the records from both the tables, or fills in NULL values for missing matches on either side.
The following query demonstrates FULL OUTER JOIN between CUSTOMER and ORDER tables:
hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE FROM CUSTOMERS c FULL OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);
On successful execution of the query, you get to see the following response:
+------+----------+--------+---------------------+ | ID | NAME | AMOUNT | DATE | +------+----------+--------+---------------------+ | 1 | Ramesh | NULL | NULL | | 2 | Khilan | 1560 | 2009-11-20 00:00:00 | | 3 | kaushik | 3000 | 2009-10-08 00:00:00 | | 3 | kaushik | 1500 | 2009-10-08 00:00:00 | | 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | | 5 | Hardik | NULL | NULL | | 6 | Komal | NULL | NULL | | 7 | Muffy | NULL | NULL | | 3 | kaushik | 3000 | 2009-10-08 00:00:00 | | 3 | kaushik | 1500 | 2009-10-08 00:00:00 | | 2 | Khilan | 1560 | 2009-11-20 00:00:00 | | 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | +------+----------+--------+---------------------+
Best,
Tyler
-
11. Re: Line Chart with more than one time dimension
Tyler Garrett Nov 29, 2017 10:50 AM (in response to Tyler Garrett)Lastly,
Prerequisites
Before proceeding with this tutorial, you need a basic knowledge of Core Java, Database concepts of SQL, Hadoop File system, and any of Linux operating system flavors.