6 Replies Latest reply: Sep 26, 2015 9:18 AM by Massimo Grossi

Date Functions

Hi, I am using Qlik Sense for inventory analysis.  We have a date field [TransDate] that filters inventory levels for that date.  I want to make a table that based on the date selected there is a comparative to last week.  So, I guess I want to create a variable that is set to the [TransDate] selected minus 7 days.  Thanks.

• Re: Date Functions

In the moment you have selected a date your are unable to sum others, because QV works this manner.  In this case you need to use set analysis to do that.

Sum({<TansDate={\$(=Max(TransDate)-7)}>} Stocks)

• Re: Date Functions

or

sum({\$<TansDate={"\$(=Date(max(TansDate)-7))"}>} Stocks)

• Re: Date Functions

Agree with the above, but syntax and date formats can be an issue to get it to work properly and i would suggest a simple transformation in the load editor to ensure your set analysis works:

If you have dates (with no timestamps), then use

Expression:     Sum({<TransDate={\$(=Date(Max(TransDate)-7))}>} Stocks)

If you have dates with timestamps but you don't care about the timestamp portions then

Expression:     Sum({<TransDate={\$(=Date(Max(TransDate)-7))}>} Stocks)

if you have timestamps and you want to keep the timestamp portion then

Expression:     Sum({<TransDate={">\$(=Timestamp(Max(TransDate)-8))<\$(=Date(Timestamp(TransDate)-6))"}>} Stocks)

• Re: Date Functions

Thanks for help.  unfortunately it is still not working for me.

We do have time stamps but do not care about them so this is how we load data.

date(floor(num(DateIn))) as DateIn,

MaterialCost,

ProductID,

SerialNbrID,

ReasonCodeID,

StoreID,

date(floor(num(TransDate))) as TransDate;

Or inventory data warehouse takes a daily snapshot of inventory.  TransDate is the day the snapshot is taken.

If I use Sum(MaterialCost) it will sum up the 90 days of inventory information in the DW.  When I use TransDate as filter I get the inventory for that day.

This formula does not work.  I if I am interpreting it correct is should give back inventory for a week earlier.  Right now I get nothing.

Sum({<TransDate={\$(=Date(Max(TransDate)-7))}>} MaterialCost)

Thanks.

• Re: Date Functions

Let's add some single quotes in there next

Sum ( {<TransDate={'\$(=Date(Max(TransDate)-7)))'}>} MaterialCost)