Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

not to filter date with other column

Hi,

I have two source tables:

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

TypeDateFlag
AA07-01-20141
AA07-02-20141
BB07-02-20141
AA07-03-20141
BB07-05-20141
CC07-07-20141
DD07-07-20141
BB07-09-20141

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:

TypeDate07-01-1407-02-1407-03-1407-04-1407-05-1407-06-1407-07-1407-08-1407-09-1407-10-14
AA 111
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 :

TypeDate07-02-1407-05-1407-09-14
BB 111

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

TypeDate07-01-1407-02-1407-03-1407-04-1407-05-1407-06-1407-07-1407-08-1407-09-1407-10-14
BB 1 1 1

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

Please Help.

-Mike

8 Replies
JonnyPoole
Employee
Employee

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
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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
Employee
Employee

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
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

No solution is working. please help.

JonnyPoole
Employee
Employee

modified technique to fit your data.  Is it accurate ?

JonnyPoole
Employee
Employee

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