topic Re: not to filter date with other column in QlikView

not to filter date with other column

Tue, 02 Sep 2014 18:49:00 GMT

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

Re: not to filter date with other column

JonnyPoole — Tue, 02 Sep 2014 18:53:35 GMT

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'

Re: not to filter date with other column

IAMDV — Tue, 02 Sep 2014 18:56:57 GMT

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,

www.QlikShare.com

Re: not to filter date with other column

Tue, 02 Sep 2014 19:04:14 GMT

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

Re: not to filter date with other column

JonnyPoole — Tue, 02 Sep 2014 19:10:11 GMT

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;

Re: Re: not to filter date with other column

IAMDV — Tue, 02 Sep 2014 19:13:38 GMT

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,

www.QlikShare.com

Re: not to filter date with other column

Tue, 02 Sep 2014 20:06:51 GMT

No solution is working. please help.

Re: Re: not to filter date with other column

JonnyPoole — Tue, 02 Sep 2014 20:17:25 GMT

modified technique to fit your data. Is it accurate ?

Re: Re: Re: not to filter date with other column

JonnyPoole — Tue, 02 Sep 2014 20:41:59 GMT

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