topic Two date ranges as dimensions in QlikView

Two date ranges as dimensions

xtrimf — Wed, 01 Jul 2009 23:51:13 GMT

Hi,

I have the folowing tables:

* Orders (OrderID, Date1, Date2)

* OrderItems(OrderID,ItemID,Item_Price)

* DateTable(Date1,year,month,quarter,etc...)

I have a chart with the quarter as the dimension an I present the sum of the items.

I want also to present on another bar that shows the same info but to use Date2 instead of Date1.

The reason is - Date1 shows the original date order. Date 2 shows the date of the billing.

Is it even possible? or do i have to build another table and show on another chart?

Tnx 4 your help

Two date ranges as dimensions

Oleg_Troyansky — Thu, 02 Jul 2009 01:19:34 GMT

Yes, it's possible, in a number of ways... Before anything else, we have to understand and agree that we have 2 different Date fields, and therefore the attributes (Year, Month, etc...) have to be different. Once we understand that, we can continue...

A few ways to resolve this problem:

1. Create a separate Calendar table for the Billing Dates and have a separate set of attributes - Billing Year, Billing Month, etc...

2. Or, you can simply join both Calendar tables into the data table and keep those attributes in the same table (you still need separate attributes for Order Date and Billing Date).

3. Finally, you can build the solution with a single Calendar, but it would be the most complex... You'd need to "normalize" your Orders table and have a separate tables for dates - one row for each date. Some thing like this:

Orders (OrderID, ... other Order Attributes)

OrderDates(OrderID, Date, DateTypeFlag), where DateTypeFlag differentiates between Order Date vs. Billing Date.

Then, in all your expressions and dimensions you'd have to use DateTypeFlag in order to specify what date needs to be used.

Even though the later solution only requires a single Calendar, it's the most complex and time consuming. I'd recommend one of the first two options, unless you have any other requirements that definitely call for a single Calendar...

regards,

Oleg

Two date ranges as dimensions

xtrimf — Thu, 02 Jul 2009 02:24:04 GMT

Hi Oleg,

I still don't see how I can manage to have "quarter" as the dimention and two different expressions that reffer to different date fields.

I don't want to have on my dimention "quarter" & " quarter2"....

Maybe method 3 will work...

Tnx

Two date ranges as dimensions

johnw — Thu, 02 Jul 2009 06:06:51 GMT

With methods 1 and 2, you would indeed have "quarter ordered" and "quarter billed". But that is, after all, "necessary" if you want to be able to select different values for each.

Method 3 won't allow your users (at least without even more complexity) to select orders that were ordered in quarter 1 and billed in quarter 2, for instance. But if that's not a problem, then I'm a big fan of just having a "Quarter" field, a single chart that lets you easily switch between types of dates, and so on. I've done it in several applications, and like the results.

But for most applications, I suspect it is better to just create more than one calendar and have a bunch of different date names.