Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Date Calculation Question

Hello Community Members!

This is my first week working with Qlik Sense Desktop and as thrilled as I am, I get stuck occasionally.  This community helped me extremely well last time and I'm hoping someone can help me with this one as well.

I have one table that uses the date field like this:

Date - Transactions.JPG

Thanks to the community, I now have that interpreted by using this formula:

    Date(DATE#(left("date/time",len("date/time")-4),'MMM DD, YYYY hh:mm:ss tt')) as Date,

But now I also have another table that makes the date like this:

Date - Ads.JPG

I'd like to think I was smart enough to figure this one out, so I used this formula:

    Date(DATE#("Start Date",'MM/DD/YYYY hh:mm')) as AdDate,

It seemed to work.  I have both tables loaded and they are connected with the key field "sku".

See below:

Data Model.JPG

NOTE:  I made the two date fields as different fields (Date for one and AdDate for the other).  That seemed to be the right thing to do.  But I'm not sure.  I'll get back to that in a minute.

At any rate, you can see that I have a list of total transactions and transactions from Paid Ads only.

The image below shows how I can see both the total transactions and the transactions from Paid Ads.

Sales vs Paid.JPG

Looking at the bar graphs above..

  • The top one uses the dimension =Day(Date)
  • The bottom one uses the dimension =Day(AdDate)

I would now like to create a third bar graph right below this that shows the difference between the two.

HERE'S MY QUESTION(S):

Should I have handled the importing of the dates differently?  If so, how?

     or

Is there a way to do the math to get the table I want above if the dates are two different fields?

I really appreciate the help and apologize if it is so newbie I should know better.  (It seems as if dates are the hard part for me.)

3 Replies
Employee
Employee

Re: Date Calculation Question

Hi Cory - you need a master calendar that introduces a new date field that links to both types of your dates

Here is a good link:

Canonical Date

This would have to be a central table that joins to both tables that have dates.  You may need to setup a central link table that has all the common dimnesions (sku, date ) or it may make sense to just merge the two tables by SKU first. There are a few options on the modelling but it depends what you are trying to do .

Either way take a look at the blog and see if that helps

Not applicable

Re: Date Calculation Question

Awesome.  I had not heard of "master calendar".  I guess I need to learn more about that.

Not applicable

Re: Date Calculation Question

Actually, it looks like the "cannonical calendar" is what I really need to do.

Community Browser