Hi

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.

Please attach your workbook in a TWBX format

Jim

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

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

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

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.

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.

HiveQL Select Joins

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

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.