topic Re: Moving sales to different dates in QlikView

Moving sales to different dates

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

I need to re-create a report that moves any sales made during a non-working day to the following working day.

For example assuming a non-working weekend:

Actual Data

Day	Sales
Fri	12,531
Sat	852
Sun	612
Mon	18,187
Tues	16,873

Desired output

Day	Sales
Fri	12,531
Sat	0
Sun	0
Mon	19,651
Tues	16,873

Re: Moving sales to different dates

aarkay29 — Tue, 28 Mar 2017 14:51:59 GMT

Try this:

Orders:

LOAD

Cust,

Net,

Date

FROM

[Data 2.xlsx]

(ooxml, embedded labels, table is Orders);

Left Join (Orders)

NonWorkingDays:

LOAD

Date,

HolidayFlag

FROM

[Data 2.xlsx]

(ooxml, embedded labels, table is NonWorkingDays);

Load

Cust,

Net,

Date,

If(peek(HolidayFlag)=1,Net+peek(NetSales),Net) as NetSales

Resident

Orders;

Drop table

Orders;

Re: Moving sales to different dates

hobanwashburne — Thu, 30 Mar 2017 13:04:51 GMT

That didn't seem to work for me

Re: Moving sales to different dates

tunoi — Thu, 30 Mar 2017 14:26:28 GMT

This works?

Orders:

LOAD

Cust,

Net,

Date

FROM

[Data 2.xlsx]

(ooxml, embedded labels, table is Orders);

left join(Orders)

LOAD

Date,

HolidayFlag

FROM

[Data 2.xlsx]

(ooxml, embedded labels, table is NonWorkingDays);

temp_calendar:

Load Distinct

Date,

HolidayFlag

resident

Orders

;

New_Date:

Load

1 as Date,

1 as New_Date

autogenerate(0)

;

for i = 0 to NoOfRows('temp_calendar') - 1

let v_date = num(peek('Date', $(i), 'temp_calendar'));

let v_holiday = alt(peek('HolidayFlag', $(i), 'temp_calendar'),0);

if $(v_holiday) = 0 then

concatenate (New_Date)

load

date($(v_date)) as Date,

date($(v_date)) as New_Date

autogenerate(1);

else

concatenate (New_Date)

load

date($(v_date)) as Date,

date(min(Date)) as New_Date

Resident

temp_calendar

where

Date > $(v_date) and HolidayFlag <> 1;

end if;

Next i;

drop table temp_calendar;

Left join(Orders)

load

Date,

New_Date

resident

New_Date

;

drop table New_Date ;

Re: Moving sales to different dates

aarkay29 — Thu, 30 Mar 2017 15:11:42 GMT

Alternate solution

PFA