Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.)

6 Replies
Employee
Employee

Re: Date Calculation Question

I wonder if your date formatting could be made a bit easier, and minimize using string functions like left() and len().

Will this work instead?


Date(Date#([date/time], 'MMM DD, YYYY hh:mm:ss tt PDT'))

One thing to keep in mind int his scenario is that you are loading timestamp values. These are numerically stored decimal values. Date are stored as integers.

My current timestamp looks like 19 May 2015 13:17:41, and has a underlying numerical value of 42143.553946759.

=Num(Date#('19 May 2015 13:17:41', 'DD MMM YYYY hh:mm:ss'))

If I have today's date 19 May 2015, the numerical value is 42143.

=Num(Date#('19 May 2015', 'DD MMM YYYY'))


You should store date values as proper dates, meaning that they have no time associated with them. Use the Floor() function to eliminate the decimal part of the value. And for clarity perhaps use the Timestamp#() function to load the first step.

Date(Floor(Timestamp#([date/time], 'MMM DD, YYYY hh:mm:ss tt PDT')))


If you then need the time and/or timestamp store them in separate fields. To make the timestamp into a time, you need to make the integer part a 0, which can be done with the Frac() function.


Timestamp(Timestamp#([date/time], 'MMM DD, YYYY hh:mm:ss tt PDT'))

Time(Frac(Timestamp#([date/time], 'MMM DD, YYYY hh:mm:ss tt PDT')))

Employee
Employee

Re: Date Calculation Question

The problem with combining your charts is that you use different dimensions in the charts. The values are not aggregated over the same timeline in this case, they look the same but are different. I do not have any quick solution to this.

I probably would have tried to adapt the data model to allow for the comparison to make both cases run towards the same calendar. This is how ever potential quite complicated, and would require analysis of the application file and data structure.

To enable yourself on more advanced data modeling I would suggest looking it attending the "Data Modeling for Qlik Sense" training at Qlik Training

Not applicable

Re: Date Calculation Question

Yes, that is the part I want help with.  Maybe there is no easy solution?

Anyone else? 

Employee
Employee

Re: Date Calculation Question

I think you will be more likely to get detailed feedback if you can attach a sample QVF file.

Not applicable

Re: Date Calculation Question

I am not familiar with QVF files.  I'll look into it.

Employee
Employee

Re: Date Calculation Question

The QVF file is the actual application file. There is one file per app that you create in Qlik Sense Desktop.

The files are stored in your document folder, with a path like below.

C:\Users\<USER>\Documents\Qlik\Sense\Apps