not to filter date with other column

[#1]

Hi,

I have two source tables:

Table1: (it's just sample, I have thousands of records)

Type	Date	Flag
AA	07-01-2014	1
AA	07-02-2014	1
BB	07-02-2014	1
AA	07-03-2014	1
BB	07-05-2014	1
CC	07-07-2014	1
DD	07-07-2014	1
BB	07-09-2014	1

Table 2: (it's just sample, I have thousands of records as all dates of about 3 years)

Date2
07-01-2014
07-02-2014
07-03-2014
07-04-2014
07-05-2014
07-06-2014
07-07-2014
07-08-2014
07-09-2014
07-10-2014
07-11-2014
07-12-2014

Then I created a Pivot table as:

Type	Date	07-01-14	07-02-14	07-03-14	07-04-14	07-05-14	07-06-14	07-07-14	07-08-14	07-09-14	07-10-14
AA		1	1	1
BB			1			1				1
CC								1
DD								1

Now, when I create a list box for 'Type' and select Type as 'BB' (or any Type) what I get output pivot table dates only specific to selected Type :

Type	Date	07-02-14	07-05-14	07-09-14
BB		1	1	1

but what I want is even after filtering Type, Date should not be filtered, and output pivot table should be:

Type	Date	07-01-14	07-02-14	07-03-14	07-04-14	07-05-14	07-06-14	07-07-14	07-08-14	07-09-14	07-10-14
BB			1			1				1

For analysis, I also want for which dates selected Type got missed

Please Help.

-Mike

[JonnyPoole]

On the 'dimensions' tab select 'show all values' so that all  dates always show.

on the presentation tab you may need to unselect ' suppress zeroes' and/or 'suppress missing'

[IAMDV]

Hi Mike,

As I understand, you're pivoting the "Date" field. Before you pivoted the "Date" field, it's in rows and once you pivoted it's moved to columns for each possible "Date". You can two options:

Option 1:

1. Enable "Show All Values" on "Date" dimension

2. Un-check "Surpress NULL values" in the presentation tab

Prefered Option 2:

For each distinct "Type" field value and concatenate this table by filling  "-" or other symbol. This way you will always have a value for each "Date" field and you will always see all the dates after pivoting.

I hope this helps!

Cheers,

DV

www.QlikShare.com

[#4]

DV,

Option 1 doesn't helps when I filter the Type.

Can you please elaborate your Option 2 so that I can give it a try..

Note: I don't have data for every date in Table1

[JonnyPoole]

If you don't have dates for every Type you'll need to build out a date calendar and use a date field with all dates. This is done in the load script. Under //logic to add all dates you will see a routine to fill in all dates within the date range in your data source.  In your chart , use 'ChartDate' instead of 'Date' as the dimension .

SourceData:

LOAD

    OrderID,

    OrderDate as "Date",

    CustomerID,

    EmployeeID,

    ShipperID,

    ProductID,

    Sales,

    Costs,

    GP,

    Quantity,

    Discount,

    Freight

FROM <sourcedata>

//logic to add all dates

MaxMinDates:

load

  max(Date) as MaxDate,

  min(Date) as MinDate

Resident SourceData;

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

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

Dates:

load

  date(RecNo()-1 + $(vMinDate)) as ChartDate

AutoGenerate ($(vMaxDate)-$(vMinDate)+1);

drop table MaxMinDates;

left join (Dates)

Load

  Date as ChartDate,

    Date

resident SourceData;

[IAMDV]

Understood! Option 1 will not work when you want to filter on "Type" field.

It's easier if you have sample QVW and I can mock up something for you. Or else, you need:

CONCATENATE

LOAD DISTINCT "Type", "Date", "-" AS Flag

Resident Table1;

You need to concatenate the above table to your main table. This way you will have "-" value no matter what you select in "Type". It might look strange to see the last row as "-" but it will give you all the rows/Dates.

I hope this makes sense!

Cheers,

DV

www.QlikShare.com

[#7]

No solution is working. please help.

[JonnyPoole]

modified technique to fit your data.  Is it accurate ?

[JonnyPoole]

Also ... there is no need for a 2nd list of dates.  I added the rest of my script to populate all dates between the min and max date in your first data source.

Check it out