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.
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
The following is a simplified model. Note that the Fact Tables only have the date column. My measures are a count of this column.
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");
- 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.