8 Replies Latest reply: Sep 2, 2014 4:42 PM by Jonathan Poole Branched to a new discussion. RSS

    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

        • Re: not to filter date with other column
          Jonathan Poole

          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
            Deepak Vadithala

            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

              • Re: not to filter date with other column

                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
                    Jonathan Poole

                    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
                      Deepak Vadithala

                      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

                  • Re: not to filter date with other column

                    No solution is working. please help.