topic Re: weekly in QlikView

weekly

santhosh_k_n — Wed, 07 May 2014 05:35:48 GMT

Hi All,

I need to present sales on weekly(last 8 weeks) basis, but the sum of sales should be taken on last day of each week.

eg:

from date: monsthsatrt(today()){01-05-2014} and todate=today()-1{06-05-2015}

week no	date/day	sales	weekly sales
wk18	01-05-2014/Fri	100
wk18	02-05-2014/Sat	150
wk18	03-05-2014/sun	175	175
wk19	04-05-2014/Mon	200
. . .
wk19	10-05-2014/Sun	250	250

Required chart as:

Please guide me.

Re: weekly

nirav_bhimani — Wed, 07 May 2014 05:49:25 GMT

Try this Expression.

=Sum({< CalendarDate={">=$(=WeekStart(Today(),-7))<=$(=Date(Today()-1))"}, Year=, Month=, Quarter=>} Sales)

add the week as Dimension.

Regards,

Nirav Bhimani

Re: weekly

santhosh_k_n — Wed, 07 May 2014 06:05:53 GMT

Hi Nirav,

Thanks for the response.

But the above expression gives sum of sales for whole last weeks but not for last 8 weeks.

and as i mentioned sum of sales should be calculated on last day of the week

Re: Re: weekly

nirav_bhimani — Wed, 07 May 2014 06:18:17 GMT

Hi,

Kindly find the attached solution.

Regards,

Nirav Bhimani

Re: weekly

santhosh_k_n — Wed, 07 May 2014 06:43:17 GMT

Hi Nirav,

This is not exactly what i needed.

I need to show in chart sales happened on last day of week.

In your example, For week 11, 15th is the last day of week and sales on 15th is 100.

so in chart i need to present for wk11 values in chart as 100 but not 450(sum of sales of all other days in week )

Hope u got this

Re: weekly

senpradip007 — Wed, 07 May 2014 06:44:26 GMT

Try to execute this script:

DateRange:
LOAD
Max([Date of issue]) As MaxDate,
Min([Date of issue]) As MinDate
Resident FactTable;

LET vMinnDate = Peek('MinDate',0,'DateRange');
LET vMaxxDate = Peek('MaxDate',0,'DateRange');

DROP Table DateRange;

Calendar:
LOAD
Year($(vMinnDate) + RecNo() - 1) &'-'& Week($(vMinnDate) + RecNo() - 1) &'-'& Day($(vMinnDate) + RecNo() - 1) AS Key,
Date($(vMinnDate) + RecNo() - 1) AS Date,
Month($(vMinnDate) + RecNo() - 1) AS Month,
Year($(vMinnDate) + RecNo() - 1) AS Year,
Week($(vMinnDate) + RecNo() - 1) AS Week,
'Q' & Ceil(Month($(vMinnDate) + RecNo() - 1)/3) AS Quarter,
Day($(vMinnDate) + RecNo() - 1) As Day

AutoGenerate ($(vMaxxDate) - $(vMinnDate) + 1);

Temp:
NoConcatenate
LOAD
Year,
Week,
Max(Day) As MaxD,
1 As MaxWeekDay
Resident Calendar

Group By Year, Week;

Left Join(Calendar)
LOAD
Year&'-'&Week&'-'&MaxD As Key,
MaxWeekDay
Resident Temp;

DROP Table Temp;

In Expressions:

Sum(({<CalendarDate={">=$(=WeekStart(Today(),-7))<=$(=Date(Today()-1))"}, MaxWeekDay={1}>} Sales)

Re: Re: weekly

nirav_bhimani — Wed, 07 May 2014 08:26:53 GMT

Hi,

Kindly find the updated solution.

Regards,

Nirav Bhimani

Re: weekly

santhosh_k_n — Thu, 08 May 2014 05:22:25 GMT

Hi All,

Thanks for ur responce.

The expression "Sum(({<CalendarDate={">=$(=WeekStart(Today(),-7))<=$(=Date(Today()-1))"}, MaxWeekDay={1}>} Sales)" is not matching with my requirement. Instead i tried as below,

1. create 8 variables with last day of the week for 8 weeks

eg: last_1week: MakeWeekDate(year(today()),week(today()-1),6)

last_2week: MakeWeekDate(year(today()),week(today()-2),6)

- - -

last_8week: MakeWeekDate(year(today()),week(today()-8),6)

2. expression:

sum( {<date= {$last_1week,$last_2week------,$last_8week}>} sales)