Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
sersal10
Creator
Creator
‎2024-04-17 04:56 AM

How to put in the same graph 2 measures with different dimensions.

Hi all,

I need to put 2 measures in a graph. These are number of tickets and budget of tickets (what we expect). The number of tickets are in a table and the dimension is weeks_left_to_event which ends up in the present. The budget fields are in another table whose field I want to use as a dimension is budget_weeks_left_to_event.

Is there a way to share the dimension as I would want both lines in a graph?

Many thanks!!

Labels (2)
Labels
228 Views
0 Likes
7 Replies
Rohan
Specialist
Specialist
‎2024-04-17 05:12 AM

Hi,

You will have to load the fact table twice & concatenate both the week fields into a single field. Then use this new Week field & your measures in the Line graph.

 

Regards,

Rohan.

 

220 Views
0 Likes
sersal10
Creator
Creator
‎2024-04-17 05:24 AM
Author

In response to Rohan

Hi Rohan!!

I didn't understand well. How would you do it?

These are the 2 tables:

 
budgets:
LOAD
 
    "Código del evento" as event_code,
    event_end_time as budget_event_end_time,
    timestamp(FECHA) AS budget_date,
    "NUMERO DE TICKETS" as budget_quantity,
    PRECIO as budget_price,
    "BOOKING FEE" as budget_fee,
    "GROSS REVENUE" as budget_gross_revenue,
    round(num(Interval(event_end_time - timestamp(FECHA), 'DD'))/7,2) AS budget_weeks_left_to_event
FROM [lib://DataFiles/Ticketing 2024.xlsx]
(ooxml, embedded labels, table is [Hoja 3]);
 
 
LOAD 
"event_id",
"event_name",
"event_country",
"event_date",
capacity,
"event_code",
"ticket_id",
"cashless_paid_credits",
quantity,
"ticket_type",
"sold_date",
"ticket_date",
"ticket_category",
status,
"event_currency",
"gbp_rate",
"eur_rate",
"base_amount",
"fee_amount",
"total_amount",
email,
"customer_country",
city,
age,
gender,
"age_group",
"year_event",
"month_event",
"days_left_to_event",
"weeks_left_to_event",
"months_left_to_event",
babb,
"previous_event_code",
"event_end_time",
"event_to_compare",
"previous_day_sales",
"marketing_total_spend",
    cpa;
    
[marts_event_ticketing]:
SELECT "event_id",
"event_name",
"event_country",
"event_date",
capacity,
"event_code",
"ticket_id",
"cashless_paid_credits",
quantity,
"ticket_type",
"sold_date",
"ticket_date",
"ticket_category",
status,
"event_currency",
"gbp_rate",
"eur_rate",
"base_amount",
"fee_amount",
"total_amount",
email,
"customer_country",
city,
age,
gender,
"age_group",
"year_event",
"month_event",
"days_left_to_event",
"weeks_left_to_event",
"months_left_to_event",
babb,
"previous_event_code",
"event_end_time",
"event_to_compare",
"previous_day_sales",
"marketing_total_spend",
cpa
FROM "dbt_xavi_marts"."marts_event_ticketing";
214 Views
Rohan
Specialist
Specialist
‎2024-04-17 05:27 AM

In response to sersal10
Try something like this :
 
budgets:
LOAD
 
    "Código del evento" as event_code,
    event_end_time as budget_event_end_time,
    timestamp(FECHA) AS budget_date,
    "NUMERO DE TICKETS" as budget_quantity,
    PRECIO as budget_price,
    'Budget' as Flag,
    "BOOKING FEE" as budget_fee,
    "GROSS REVENUE" as budget_gross_revenue,
    round(num(Interval(event_end_time - timestamp(FECHA), 'DD'))/7,2) AS budget_weeks_left_to_event,
round(num(Interval(event_end_time - timestamp(FECHA), 'DD'))/7,2) AS WEEK
FROM [lib://DataFiles/Ticketing 2024.xlsx]
(ooxml, embedded labels, table is [Hoja 3]);
 
Concatenate(budgets)
LOAD 
"event_id",
"event_name",
"event_country",
"event_date",
capacity,
"event_code",
"ticket_id",
"cashless_paid_credits",
quantity,
"ticket_type",
"sold_date",
"ticket_date",
"ticket_category",
status,
"event_currency",
"gbp_rate",
"eur_rate",
"base_amount",
"fee_amount",
"total_amount",
email,
"customer_country",
city,
age,
gender,
"age_group",
"year_event",
"month_event",
"days_left_to_event",
'Revenue' as Flag,
"weeks_left_to_event",
"weeks_left_to_event" as WEEK,
"months_left_to_event",
babb,
"previous_event_code",
"event_end_time",
"event_to_compare",
"previous_day_sales",
"marketing_total_spend",
    cpa;
    
[marts_event_ticketing]:
SELECT "event_id",
"event_name",
"event_country",
"event_date",
capacity,
"event_code",
"ticket_id",
"cashless_paid_credits",
quantity,
"ticket_type",
"sold_date",
"ticket_date",
"ticket_category",
status,
"event_currency",
"gbp_rate",
"eur_rate",
"base_amount",
"fee_amount",
"total_amount",
email,
"customer_country",
city,
age,
gender,
"age_group",
"year_event",
"month_event",
"days_left_to_event",
"weeks_left_to_event",
"months_left_to_event",
babb,
"previous_event_code",
"event_end_time",
"event_to_compare",
"previous_day_sales",
"marketing_total_spend",
cpa
FROM "dbt_xavi_marts"."marts_event_ticketing";
 
Regards,
Rohan.
208 Views
sersal10
Creator
Creator
‎2024-04-17 05:33 AM
Author

In response to Rohan

Hi Rohan, many thanks for your answers

 

I tried that before but I need some other fields from the fact table in the budget rows as event_name, event_year, event_to_compare... so that the rows with the budget are not very empty. maybe I should do a left join before, with only these (dimension) fields form the big table, and then concatenate as you said.

Thanks again

 

201 Views
0 Likes
sersal10
Creator
Creator
‎2024-04-17 05:44 AM
Author

In response to Rohan

I will try this:

LIB CONNECT TO 'Amazon_Redshift_redshift-dwh.cwafb1bxivm3.eu-west-1.redshift.amazonaws.com';
 
 
LOAD 
"event_id",
"event_name",
"event_country",
"event_date",
capacity,
"event_code",
"year_event",
"month_event",
"days_left_to_event",
"weeks_left_to_event",
"months_left_to_event",
"previous_event_code";
 
    
[marts_event_ticketing]:
SELECT
"event_id",
"event_name",
"event_country",
"event_date",
"capacity",
"event_code",
"year_event",
"month_event",
"days_left_to_event",
"weeks_left_to_event",
"months_left_to_event",
"previous_event_code"
FROM "dbt_xavi_marts"."marts_event_ticketing";
 
left join 
 
LOAD
 
    "Código del evento" as event_code,
    event_end_time as budget_event_end_time,
    timestamp(FECHA) AS budget_date,
    "NUMERO DE TICKETS" as budget_quantity,
    PRECIO as budget_price,
    "BOOKING FEE" as budget_fee,
    "GROSS REVENUE" as budget_gross_revenue,
    round(num(Interval(event_end_time - timestamp(FECHA), 'DD'))/7,2) AS weeks_left_to_event
FROM [lib://DataFiles/Ticketing 2024.xlsx]
(ooxml, embedded labels, table is [Hoja 3]);
 
concatenate(marts_event_ticketing)
 
 
LOAD "event_id",
"event_name",
"event_country",
"event_date",
capacity,
"event_code",
"ticket_id",
"cashless_paid_credits",
quantity,
"ticket_type",
"sold_date",
"ticket_date",
"ticket_category",
status,
"event_currency",
"gbp_rate",
"eur_rate",
"base_amount",
"fee_amount",
"total_amount",
email,
"customer_country",
city,
age,
gender,
"age_group",
"year_event",
"month_event",
"days_left_to_event",
"weeks_left_to_event",
"months_left_to_event",
babb,
"previous_event_code",
"event_end_time",
"event_to_compare",
"previous_day_sales",
"marketing_total_spend",
    cpa;
    
[marts_event_ticketing]:
SELECT "event_id",
"event_name",
"event_country",
"event_date",
capacity,
"event_code",
"ticket_id",
"cashless_paid_credits",
quantity,
"ticket_type",
"sold_date",
"ticket_date",
"ticket_category",
status,
"event_currency",
"gbp_rate",
"eur_rate",
"base_amount",
"fee_amount",
"total_amount",
email,
"customer_country",
city,
age,
gender,
"age_group",
"year_event",
"month_event",
"days_left_to_event",
"weeks_left_to_event",
"months_left_to_event",
babb,
"previous_event_code",
"event_end_time",
"event_to_compare",
"previous_day_sales",
"marketing_total_spend",
cpa
FROM "dbt_xavi_marts"."marts_event_ticketing";
 
Fingers crossed. XD
 
 
 
 
 
 
 
187 Views
0 Likes
Rohan
Specialist
Specialist
‎2024-04-17 05:53 AM

In response to sersal10

I suspect it won't give the desired output. Hope it works out for you 

Regards,

Rohan.

181 Views
sersal10
Creator
Creator
‎2024-04-17 06:45 AM
Author

In response to Rohan

yeah it only gives the budget fields. Very weird.

Thank you for your help anyways

169 Views
0 Likes