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

[Anonymous]

if you define a mastercalendar with all Dates it should be possible

you should unclick "suppress Zero values" (presentation tab) or "show all values" for Dimension if available

[MK_QSL]

If you have all Dates associated with either A or B, simply untick Suppress Zero Values from Presentation Tab.

But if you don't have and value Associated with A and B for all Dates, you need to create those records with null values.

Generating Missing Data In QlikView

[#4]

Using a master calendar will help.

[Anonymous]

Hi thanks for your suggestion,

I have try to left join my master calendar with my data.

The structure of my data will be like this

DATE               DIM1          QTY  

26 Oct 2015              A                  20        

27 Oct 2015               A                    1

28 Oct 2015               -                      -

29 Oct 2015               -                      -

30 Oct 2015               -                      -

31 Oct 2015               -                      -

But the problem is  i need to show DIM1 on my pivot table. and it must be not null

any idea ?

Thanks in advance

[#6]

Can you attach a sample app to test ideas on.

[Anonymous · Accepted solution]

Hi,

Here is my data

The result I expect is like this

Desc         Qty          Date               Week

A               -             16-11-2015          47

A               -             17-11-2015          47

A               -             18-11-2015          47

A               1            19-11-2015          47

A               -             20-11-2015          47

A               2            21-11-2015          47

A               -             22-11-2015          47

B               -             16-11-2015          47

B               -             17-11-2015          47

B               -             18-11-2015          47

B               -             19-11-2015          47

B               3            20-11-2015          47

B               -             21-11-2015          47

B               4            22-11-2015          47

Thanks in advance

Really appreciate your help.

[MayilVahanan]

Hi

Try like this

Data:

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

     Week

FROM

[Left Join Data.xlsx]

(ooxml, embedded labels, table is Sheet2);

left join(Data)

LOAD Desc,

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

     Qty

FROM

[Left Join Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

MaxDateTmp:

Load Max(Date) as MaxDate, Min(Date) as MinDate Resident Data;

Let vMaxDate = Peek('MaxDate', -1, 'MaxDateTemp');

Let vMinDate = Peek('MinDate', -1, 'MaxDateTemp');

Join(Data)

LOAD Desc as Desc, Week( Date($(vMinDate) + IterNo()-1)) as Week, Date($(vMinDate) + IterNo()-1) as Date Resident Data While ($(vMinDate) + IterNo()-1) <= $(vMaxDate);

FinalData:

NoConcatenate

Load * Resident Data Where not isnull(Desc );

DROP Table Data, MaxDateTmp;

Output:

Desc	Qty	Date	Week
A		16/11/2015	47
A		17/11/2015	47
A		18/11/2015	47
A	1	19/11/2015	47
A		20/11/2015	47
A	2	21/11/2015	47
A		22/11/2015	47
B		16/11/2015	47
B		17/11/2015	47
B		18/11/2015	47
B		19/11/2015	47
B	3	20/11/2015	47
B		21/11/2015	47
B	4	22/11/2015	47

[tyagishaila]

you can autogenerate whole week dates or complete month dates..

Load Desc,

        Date,

        Qty

from .....................qvd;

outer join

NewDate:

Load

       Date(today()-RecNo(), 'DD-MM-YYYY') as Date

       Autogenerate 7 ;

Note: 7 used to generate 7 dates, if you want 1 month dates use 30 in place of 7.

[Anonymous]

Hi,

Please see the below post:

The Master Calendar