Date Functions

[gbedford]

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.

[jolivares]

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.

[maxgro]

or

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

[JonnyPoole]

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]

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.

[JonnyPoole]

Let's add some single quotes in there next

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

[maxgro]

It seems it works, see also the attachment