Varying Costum SQL Query

jcatulo · May 29, 2023, 7:11pm

Hi!

I am trying to create a dataset using a Costum SQL query. However, this query is not static, it varies depending on the date of the current week. In other words, if the week belongs to two different months (for example, the week of May 28 to April 4), the structure of the query must be different from the query of a week that belongs only to one month.

Here is an illustration of the two different queries:

Query for the weeks that belong only to one month

SELECT T1.column_1, T1.column_2
FROM table1 as T1
WHERE T1.year = YEAR(current_date) 
         AND T1.month = MONTH(current_date)
         AND T1.day BETWEEN DAY(current_date - interval '7' day ) AND DAY(current_date)

Query for the weeks that belong to two different months

SELECT T1.column_1, T1.column_2
FROM table1 as T1
WHERE T1.year = YEAR(current_date) 
         AND T1.month IN (MONTH(current_date - interval '7' day), MONTH(current_date) )
         AND ( (T1.month = MONTH(current_date - interval '7' day) AND T1.day BETWEEN DAY(current_date - interval '7' day) AND 31) 
               OR (T1.month = MONTH(current_date) AND T1.day BETWEEN 1 AND DAY(current_date) )

How can I create this Costum SQL query to create my dataset?

If my question was not very clear, please feel free to ask me any questions.

Sanjeeb2022 · May 29, 2023, 7:36pm

Hi @jcatulo - Thanks for the question. Looks like depending upon the condition, you want to run specific sql, I do not think it is possibly in QuickSight custom SQL. Can you handle it at ETL layer .

Hi @David_Wong @Naveed - Please share your feedback on this.

Regards -Sanjeeb

Naveed · May 30, 2023, 4:35am

Hi, @jcatulo

You can you use the any type of query(simple or complex) in direct query method in your dataset.
Secondly you can create view in your database if you using the db at the backend.
regards,
Naveed Ali

jcatulo · May 30, 2023, 7:15am

Hi @Naveed!

Thanks for the answer. However I didn’t understand how I can get over this problem. Can you explain it better?

I am sorry for the inconvenience.

Naveed · May 30, 2023, 7:20am

Hi,

Create a database view and call in dataset set SPICE. If not clear please give some more details.

regards,
Naveed Ali

jcatulo · May 30, 2023, 7:35am

Thank you for the answer.

I have a varying query that depends on the current date. How can I define my Costum SQL query to create a dataset in SPICE?

Koushik_Muthanna · May 30, 2023, 12:50pm

Hi @jcatulo ,

From what I understand, your custom sql is filtering data at different time intervals on the same data . At times you are looking at data for a month , at times you are looking at data for 2 months.
Take a look at period over period calculations that are possible and test if use-case is resolved by building them in QuickSight analysis. : Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)

If the above does not solve your use case , you can build the views in your backend database . Create an individual SPICE dataset for each view.

Kind regards,
Koushik

jcatulo · June 1, 2023, 2:16pm

Thanks for the answer, @Koushik_Muthanna.

However the period over period calculations do not solve my problem.

As for your second suggestion, I already created an individual SPICE dataset for each view, but another problem arises. Let us call the SPICE dataset containing data from one month dataset_1, and the other dataset_2. I want to create one single visual. If the data belongs only to one month, I want the data from dataset_1 to show in that visual. If the data belongs to two months, I want the data from dataset_2 to show.

My ideia was to create a calculated field that verified if the first day of the current week belongs to the current month (let us call this field month_condition). Then, I would create two different visuals (one with data from dataset_1 and the other with data from dataset_2) and I would define a Rule that would hide one of them based on the field month_condition. However, I cannot define rules based on dataset fields, only on the analysis’ parameters.

How can I get over this?

Koushik_Muthanna · June 2, 2023, 1:38pm

@jcatulo ,

Maybe I am missing information, but you could use a single dataset and filter the data based on the date of the current week in a QuickSight analysis . Not sure why you need custom sql.

Query for the weeks that belong only to one month
If we take the example that you are reporting on May 26th, your query will retrieve data from 19th May 2023 till 26th May 2023

Query for the weeks that belong to two different months
If we take the example for week of May 28th to June 4th and you are reporting on 2nd June . Your query will retrieve data from 26th May 2023 till 2nd June 2023

Here is an example below

QS_Reporting_Based_On_Week

Regarding hiding visuals, as you have noted it’s only possible through parameters . You can look at this demo : https://democentral.learnquicksight.online/#Dashboard-FeatureDemo-Free-form-Theater-Seating , probably you get an idea how to use it for your 2 visuals with a combination of actions.

Kind regards,
Koushik

jcatulo · June 5, 2023, 9:23am

Thanks for the suggestion @Koushik_Muthanna.

However, I cannot use a single dataset. My dataset is extremely large and it would be very expensive to import it to SPICE. My only option would be to filter the data using Costum SQL.

So, my idea was to create two different dataset views and to import it to SPICE. Then, I would create two visuals (one for each dataset view) and I would hide one of them based on a specific condition. In this case, the condition would be if the first day of the week belonged to the current month or not.

I am having a lot of difficulties overcoming this challenge. Is there any other idea about how I can get over this problem?

Thank you in advance.

Koushik_Muthanna · June 5, 2023, 9:31am

@jcatulo ,
in the above example I am not using SPICE . It is direct query and applying filters will reduce the amount of data retrieved.

Additionally for custom sql you can also have dataset parameters which could further help with filtering of data initially : Using dataset parameters in Amazon QuickSight - Amazon QuickSight , Youtube video : QuickSight Dataset Parameter: 2023 Amazon QuickSight Learning Series - YouTube

Kind regards,
Koushik

jcatulo · June 5, 2023, 6:55pm

I was finally able to surpass this challenge. My initial problem was having two different queries (one query for when the current week belongs to the current month and other query for when it also belongs to the previous month) and not being able to create a Costum SQL query that embraced both options.

However, I came up with a “simple” query that solved my problem:

SELECT T1.column_1, T1.column_2
FROM table1 as T1
WHERE ( DAY(current_date) > 7
         AND T1.year = YEAR(current_date) 
         AND T1.month = MONTH(current_date)
         AND T1.day BETWEEN DAY(current_date - interval '7' day ) AND DAY(current_date)
       ) 
      OR 
      ( DAY(current_date) <= 7
        AND T1.year = YEAR(current_date) 
         AND T1.month IN (MONTH(current_date - interval '7' day), MONTH(current_date) )
         AND ( (T1.month = MONTH(current_date - interval '7' day) AND T1.day BETWEEN DAY(current_date - interval '7' day) AND 31) 
               OR (T1.month = MONTH(current_date) AND T1.day BETWEEN 1 AND DAY(current_date) )
      )

I basically used a OR statement to join together my two different conditions.

Koushik_Muthanna · June 5, 2023, 7:39pm

@jcatulo awesome happy to hear you solved it

Sanjeeb2022 · June 5, 2023, 7:58pm

This is an awesome solution @jcatulo .