Monthly vs monthly

I'm running into an issue I can't figure out - I need to compare 2 monthly counts vs each other and get a ratio. 

 

Search 1: Store Key = 123456 Count calls monthly (by call date)

For store 123456 they had 40,000 calls in Sep 2019

 

Search 2: Store Key = 123456 Count Applications monthly (By application create date)

For Store 123456 they had 20,000 applications in Sep 2019. 

I need to combine these 2 searches together and create a formula of (Call/App) to show that for every call in Sep 2019 each call generated .5 applications.

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • what is your data model? Do you have a calls fact and an applications fact. If so you could have a date dimension that would join to each fact. I.e. Calls joins on call date and applications on create date. Then if you select September from the date dimension you should be able to have a count(calls) / count(applications)

    Reply Like 1
  • We have these 2 datasets split into 2 different tables, we also have a DimDate table I could use, but I can't see how to specify that my date table is my main table & to join the 2 other tables on their date fields. 

    Reply Like
  • The following is a simplified model. Note that the Fact Tables only have the date column. My measures are a count of this column.

     

    DDL

    CREATE DATABASE community_monthly_question;
    
    USE community_monthly_question;
    
    CREATE FACT TABLE community_monthly_calls (
    
        "Call_Date" DATE
    
    );
    
    CREATE FACT TABLE community_monthly_applications (
    
        "Application_Date" DATE
    
    );
    
    CREATE DIMENSION TABLE community_monthly_date (
    
        "Common_Date" DATE,
    
        PRIMARY KEY ("Common_Date")
    
    );
    
    ALTER TABLE "community_monthly_calls" ADD CONSTRAINT "fk_common_date" FOREIGN KEY ("Call_Date")
    
    REFERENCES "community_monthly_date" ("Common_Date");
    
    ALTER TABLE "community_monthly_applications" ADD CONSTRAINT "fk_common_date" FOREIGN KEY ("Application_Date")
    
    REFERENCES "community_monthly_date" ("Common_Date");

    Worksheet 

    • Common_Date column from Date dimension
    • # Applications = count(application_date ) 
    • # Calls = count(call_date ) 
    • % Ratio = safe_divide (# calls , # applications )

    Result - refer to screen shot below. 

    • note that monthly is referencing the common_date field. 
    • you can then change the aggregation from monthly to yearly etc.
    Reply Like 1
Like Follow
  • Status Answered
  • 1 mth agoLast active
  • 3Replies
  • 18Views
  • 2 Following