Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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'
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
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
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;
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
No solution is working. please help.
modified technique to fit your data. Is it accurate ?
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