Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

1 Solution

Accepted Solutions
robert99
Valued Contributor II

Re: Date filters

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

12 Replies

Re: Date filters

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


talk is cheap, supply exceeds demand
shaik_basha
Contributor III

Re: Date filters

Hi tiago,

   you can solve this issue by common calendar.

SHAIK

shaik_basha
Contributor III

Re: Date filters

Hi taigo,

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

Not applicable

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?

Not applicable

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?

MVP
MVP

Re: Date filters

I'm unclear what you're saying with the December/July thing.  But attached is an example of building a linkage table very similar to what Rob was suggesting in the other thread.  It's using a date type instead of his counters, but it's the same idea.  Your key structure is simpler than the example, so yours should be simpler than this.

Not applicable

Re: Date filters

I can't open the file since I'm using personal edition.

As for the December/July thing, if I only have records for July in a table/chart and in the month listbox I select December while I'm using a trigger, the chart I mentioned before won't change meaning it'll still show July instead of December.

shaik_basha
Contributor III

Re: Date filters

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

Not applicable

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

Community Browser