2 Replies Latest reply: Apr 8, 2011 4:27 AM by Gerhard Laubscher RSS

    Subtract last cycle date from today's date

    Gerhard Laubscher

      Hi, I have the abovedata ina QV document.

      I would like to the summary boxes (can be new boxes as well) the number of days since the cycle started, and then the average purchases & payments for the day. Our cycle starts on the 7th every month, so in this example it will be one day. I really do not want to tamper with the data itself in any way. I import the daily account extract into this QV document daily (this is one of the sheets), so it needs to look at whatever the current day's date is, and subtract it from the last cycle date. So today it will be 1, on the 4th of May it will be 27.

      Our date format (another big problem I have) is 07-Apr-2011, not 07-04-2011. At the moment I cannot get qlikview to see our dates as dates, it sees it only as text items.

        • Subtract last cycle date from today's date
          Gerhard Laubscher

          Wrote that a bit hastily, please excuse the mistakes. Just to be clear:

          I want to see the average purchase and payment values PER day, e.g. if total purchases is R100,000, number of days is 10, then average is R10,000 per day.

          I see now my average purcahse price is incorrect, I need to fix that.

           

            • Subtract last cycle date from today's date
              Gerhard Laubscher

              Sorry for all the posts - but I realize now that both my average purchase and payment and minimum purchase and payment prices are incorrect, because it also sees the R0.00 as a value. So for average price it devides by the total number of customers, not total number of purchases for the cycle. and for minimum it obviously returns the zero value.

              Ideally I need the list boxes to exclude zero values - but I cannot do this when importing the data - I still need to import it so we can see who hasn't spent yet, etc. Have no idea how to fix this.