12 Replies Latest reply: Oct 30, 2012 12:53 PM by Tiago Santos RSS

    Date filters

      Hello.

       

      I have a QV10 project with more or less the model below:

       

      tableA
      ItemId
      OrderDateTime
      Brand
      Quantity
      Size
      tableB
      ItemId
      ShipmentDateTime
      City
      Cost
      Calendar
      Year
      Month
      Day
      Hour

       

      The fields in the "Calendar" table are also fields that the user can filter in the page as well as fields "Brand", "Quantity" and "Size".

       

      I have 2 charts, one referencing tableA and the other one referecing tableB. In the first chart I have a dimension "Month/Year OrderDate" and an expression SUM( Quantity ) and in the second I have a dimension "Month/Year ShipmentDate" and an expression SUM( Cost ).

       

      How can I filter the results in my charts with the respective dates (chart1 - OrderDate and char2 - ShipmentDate) selected by the user? :s

       

      Thanks in advance

        • Re: Date filters
          Gysbert Wassenaar

          If you want to select one date in a listbox and cause that to select that date in both OrderDateTime and ShipmentDateTime, look here.

            • Re: Date filters

              Thanks for your answer but I tried that before and I had a big problem that was the fact that if i had only records for the month of July for example in tableB and the user choose the month of December, QlikView won't make it a null chart, instead it will stick with the July results... maybe i did something wrong?

            • Re: Date filters
              Khadar basha shaik

              Hi tiago,

               

                 you can solve this issue by common calendar.

               

               

               

              SHAIK

              • Re: Date filters
                Khadar basha shaik

                Hi taigo,

                find attachment of excel,i give data model for your solution.

                 

                 

                  • Re: Date filters

                    thanks for your response shaik but if i do that i'll link the two tables by date which is not what i pretend since i would only get results if both results tables had the same date which isn't true since the shipment date is further to the order date. am i wrong?

                  • Re: Date filters
                    Khadar basha shaik

                    Hi tiago,

                       how you want to show the report i need some clarifications,

                     

                    1.you want to see same month order and shipment or other way

                     

                    can you give me clarification,

                     

                    your business logic is correct oredr  date after only shipment date,but in reports we are showing howmutch order in this month and howmutch shipment in this month,

                    if your requirement is different can please provide clarification,

                     

                    Thanks

                     

                    SHAIK

                      • Re: Date filters

                        Hi Shaik.

                         

                        I want to see sum(quantity) by month of orderdatetime (no matter what shipmentdatetime ) in chartA and um(cost) by month of shipmentdatetime (no matter what orderdatetime) in chartB...

                         

                        So if I link the two dates I would restrict the results to both the dates being equal (and i already have the link key - itemId).

                         

                        Thanks in advance

                          • Re: Date filters

                            I think I've managed a way to do this.

                             

                            I the model exactly as described in the main post but I separated the dates in Year/Month/Day and in each expression of each chart I have set analysis doing the filter.

                             

                            This means that, in tableA I have OrderDateTimeYear, OrderDateTimeMonth and OrderDateTimeDay and in tableB I have ShipmentDateTimeYear, ShipmentDateTimeMonth, ShipmentDateTimeDay.

                             

                            With the use of the P function in set analysis, I can assign each date values to the ones the user has choosed with the expressions below:

                             

                            TableA Expression:

                            Sum({$<OrderDateTimeYear=P(Year),OrderDateTimeMonth=P(Month),OrderDateTimeDay=P(Day)>} Quantity)

                             

                            TableB Expression:

                            Sum({$<ShipmentDateTimeYear=P(Year),ShipmentDateTimeMonth=P(Month),ShipmentDateTimeDay=P(Day)>} Cost)

                             

                             

                            If I'm not thinking right or this has some major flaw i don't see, please feel free to tell.

                             

                            I will have to make a lot of tests anyway before I can said this is done. I'll give you feedback once I finish them (next week)

                             

                            Thanks

                              • Re: Date filters
                                Robert Hutchings

                                I have a similar issue (this may be helpful)

                                 

                                http://community.qlik.com/message/274884#274884

                                 

                                 

                                 

                                What I did is set up 2 link calendar tables with different names (one for orders one for shipments)

                                 

                                so the only new field in table A would be

                                 

                                dayname (orderdatetime) as Orderdate,  //change time and date to date

                                 

                                and table B

                                 

                                dayname (shipmentdatetime) as Shipdate,  //change time and date to date

                                 

                                Two calendars tables (for shipments and orders mth day and year etc) would then be linked to Orderdate and Shipdate (as explained in the attached thread)

                                 

                                Expression

                                 

                                One calendar would be the main filter calendar (say orderCalendar)

                                 

                                Then change the filter expressions for shipments as follows

                                 

                                {$<ShipDay =  p(OrderDay)  ,  OrderDay = ,
                                etc FOR MONTH AND YEAR >}