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

    Date Functions

    greg bedford

      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
          Juan Olivares

          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.

            • Re: Date Functions
              Massimo Grossi

              or

               

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

              • Re: Date Functions
                Jonathan Poole

                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)

              • Re: Date Functions
                greg bedford

                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.