topic Re: Date filters in QlikView

Date filters

Wed, 24 Oct 2012 17:54:50 GMT

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 — Wed, 24 Oct 2012 18:18:17 GMT

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

Anonymous — Wed, 24 Oct 2012 19:14:35 GMT

Hi tiago,

you can solve this issue by common calendar.

SHAIK

Re: Date filters

Anonymous — Wed, 24 Oct 2012 19:36:11 GMT

Hi taigo,

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

Re: Date filters

Wed, 24 Oct 2012 21:30:57 GMT

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

Wed, 24 Oct 2012 21:33:26 GMT

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

johnw — Thu, 25 Oct 2012 00:10:24 GMT

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.

Re: Date filters

Thu, 25 Oct 2012 08:31:05 GMT

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.

Re: Date filters

Anonymous — Thu, 25 Oct 2012 15:27:00 GMT

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

Thu, 25 Oct 2012 17:14:04 GMT

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

Fri, 26 Oct 2012 15:15:08 GMT

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

robert99 — Sun, 28 Oct 2012 11:08:39 GMT

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

Re: Date filters

Tue, 30 Oct 2012 16:53:10 GMT

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