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

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

12 Replies
Not applicable
Author

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

robert99
Specialist III
Specialist III

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

Not applicable
Author

That's more or less what I've implemented in my project RJ.

But in my case I have only 1 calendar.

Anyway, thanks all for your help