Show a whole Week Data

[Anonymous]

Hi I have a question,

i have data in pivot table like this:

DIM3                                                 A                    B          

DIM1          DATE

A                26 Oct 2015               10                   20          

                   27 Oct 2015                1                    1

I only have two data on that week (26 Okt and 27 Okt), but i want to show a whole week, so the result will be like this:

DIM3                                                  A                    B          

DIM1          DATE

A                26 Oct 2015               10                   20          

                   27 Oct 2015               1                    1

                   28 Oct 2015               -                         -

                   29 Oct 2015               -                         -

                   30 Oct 2015               -                         -

                   31 Oct 2015               -                         -

If there is no data on that day, it will show "-"

Is it possible to make it ?

Really appreciate your help.

Thanks

[Kushal_Chawda]

like this

Data:

LOAD date(date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY') as Date,

    Week

FROM

(ooxml, embedded labels, table is Sheet2);

Join

LOAD Desc,

    date(date#(Date,'DD-MM-YYYY'),'DD-MM-YYYY') as Date,

    Qty

FROM

(ooxml, embedded labels, table is Sheet1);

MinMax:

LOAD max(Date) as MaxDate,

min(Date) as MinDate

Resident Data;

let vMaxDate= Peek('MaxDate',0,'MinMax');

let vMinDate= Peek('MinDate',0,'MinMax');

New:

LOAD Distinct Desc,

Week,

date($(vMinDate)+IterNo()-1,'DD-MM-YYYY') as Date

Resident Data

While $(vMinDate)+IterNo()-1<=$(vMaxDate) and not IsNull(Desc);

Left Join

LOAD Distinct

    Date,

    Desc,

    Qty

Resident Data;

DROP Table Data,MinMax;