topic Re: Week to Previous date and month to previous date in QlikView

Week to Previous date and month to previous date

anuradhaa — Tue, 08 Dec 2015 05:35:14 GMT

I want to get average sales for month to previous date. and week to previous date.

I know how to do this for Month to date and week to date. i have used

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Num(WeekStart(Max(mydate_NUM))))<=$(=Max(mydate_NUM))"}>} (sales), 0.01))

and my script i have used

Floor(mydate) AS mydate_NUM,

How can i do this for previous date

Re: Week to Previous date and month to previous date

Anonymous — Tue, 08 Dec 2015 05:45:57 GMT

Previous date means?

Could you explore more?

The expression you have written is: week till date

Do you want Month Till date as well?

Re: Week to Previous date and month to previous date

Anonymous — Tue, 08 Dec 2015 05:47:22 GMT

see this, might be helpful for you:

YTQ, QTD, MTD and WTD

Re: Week to Previous date and month to previous date

anuradhaa — Tue, 08 Dec 2015 05:53:40 GMT

Hi,

When i use that it will display all the records till the reload time. but i want the records till the previous day,

say i run this at 05.00 am 2015/12/08 i want the data till 11.59 pm 2015/12/07. so that means i want to remove the records that lies in 2015/12/08

Re: Week to Previous date and month to previous date

Anonymous — Tue, 08 Dec 2015 05:57:30 GMT

then simply minus 1 from max date like:

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Num(WeekStart(Max(mydate_NUM))))<=$(=Max(mydate_NUM)-1)"}>} (sales), 0.01))

Re: Week to Previous date and month to previous date

anuradhaa — Tue, 08 Dec 2015 06:09:10 GMT

No, That is not correct.

If it reloads at 2015-12-08 04.00 am and there are no records for 2015-12-08 then the max(mydate) is 2015-12-07.

So when we subs-tact 1 from that then it will not show previous day records.

Re: Week to Previous date and month to previous date

MayilVahanan — Tue, 08 Dec 2015 06:12:13 GMT

Try like this

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Floor(WeekStart(Max(mydate_NUM))))<=$(=Max(mydate_NUM)-1)"}>} (sales), 0.01))

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Floor(MonthStart(Max(mydate_NUM))))<=$(=Max(mydate_NUM)-1)"}>} (sales), 0.01))

Edit:

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Floor(WeekStart(Today())))<=$(=Floor(Today()-1)"}>} (sales), 0.01))

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Floor(MonthStart(Today())))<=$(=Floor(Today()-1)"}>} (sales), 0.01))

Re: Week to Previous date and month to previous date

tyagishaila — Tue, 08 Dec 2015 06:13:17 GMT

Hi Anuradha,

You can use ReloadTime() in place of Max(mydate_NUM)

and for previous date you can use variable in script

LET vPrevDay = date(ReloadTime()-1);

Re: Week to Previous date and month to previous date

Kushal_Chawda — Tue, 08 Dec 2015 06:30:44 GMT

try this

(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=WeekStart(Max(mydate_NUM)))<=$(=Max(mydate_NUM)-1)"}>} (sales), 0.01))

Re: Week to Previous date and month to previous date

Anonymous — Tue, 08 Dec 2015 06:46:36 GMT

use today() instead of =Max(mydate_NUM)