topic Re: Compare sales with previous year in QlikView

Compare sales with previous year

kostiskampouris — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

A few months before and with a lot of help from the community i created a document.where more or less the user selects a day(trn_date) and through a button which gives value to a variable vdatetodate sees either the selected day's sold quantity vs the same weekday's last year quantity (vdatetodate=0) or the selected day's quantity vs the the same date's last year quantity (vdatetodate=1) .

selected date 03/03/2018

The set analysis I have used is :

pick (datetodate,

Sum({<trn_date = {"$(=TimeStamp(Max(trn_date-364), 'DD/MM/YYYY'))"}>} qty),//compares with same weekday last year

Sum({<trn_date = {"$(=AddMonths(Max(trn_date), -12))"}>}qty)//compares with exact same date last year

)

My problem is that when vdatetodate=1 I get no Qty for last year.This problem apeared only after 2017 ended.

(For example for any day of the 12/2017 works perfect).

I have checked and the data are correct!

Also my date as I insert it from sql server has the format for example 2018-03-03 00:00:00.000 ( I dont know if may this is the problem)

Can someone please suggest a solution ?

Re: Compare sales with previous year

sunny_talwar — Mon, 05 Mar 2018 12:16:32 GMT

Is this the expression which isn't working?

May be you need date formatting for this also?

Sum({<trn_date = {"$(=TimeStamp(AddMonths(Max(trn_date), -12), 'DD/MM/YYYY'))"}>}qty)

Re: Compare sales with previous year

kostiskampouris — Mon, 05 Mar 2018 12:49:59 GMT

Thank you Sunny!

To be honest I tried it before but I was getting zeros...only after I copy and paste your answer worked.

Thank you again so much !!

Re: Compare sales with previous year

sunny_talwar — Mon, 05 Mar 2018 12:51:53 GMT

May be you misplaced parenthesis... but glad it worked now.

Best,

Sunny