Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gbedford
New Contributor

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.

6 Replies
Highlighted
jolivares
Valued Contributor

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)

This could solve your problem.

MVP
MVP

Re: Date Functions

or

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

Employee
Employee

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

Load editor:     Date(TransDate) as TransDate

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

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

Load editor:     Date(floor(TransDate)) as TransDate

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


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


Load editor:     Timestamp(TransDate)) as TransDate

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

gbedford
New Contributor

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.

LOAD

    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.

Employee
Employee

Re: Date Functions

Let's add some single quotes in there next

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

MVP
MVP

Re: Date Functions

It seems it works, see also the attachment

1.png

Community Browser