topic Re: Problem with Dates in QlikView

Problem with Dates

Tue, 09 Aug 2011 13:49:35 GMT

Hello,

I have two dates.

BDate = Creation Date of an order

QDate = Confirming Date of an order

QDate >= BDate

BDate QDate OrderCounter

06-30 07-02 1

07-01 07-01 1

07-01 07-02 1

07-02 07-02 1

07-02 07-03 1

07-03 07-03 1

07-04 07-04 1

I am chosing the BDate, e.g. 07-02. With this I get all orders created at that day (blue). No problem so far.

Now I additionally would like to see all orders confirmed at 07-02 (QDate = 07-02, (bold)). For doing this, I don't want to chose 07-02 as QDate, I want to do it with the old selection (BDate = 07-02). You see above, there are several values outside the selection, which are bold but not blue, I mean when QDate > BDate. These Dates also have to be in this QDate Analysis.

Is there a chance to "transmit" the "BDate" into the "QDate" and make two parallel tables, one with BDate and one with the QDate?

For the experts: Can I do this also with a range of dates, I mean with more than one date chosen?

E.g. I chose as BDate 07-01 and 07-02 and 07-03.

In the example I would get

BDate Sum(OrderCounter)

07-01 3

07-02 2

07-03 1

QDate Sum(OrderCounter)

07-01 2

07-02 3

07-03 2

Thanks for any idea

Kind regards,

Philipp

Re: Problem with Dates

IAMDV — Tue, 09 Aug 2011 14:04:05 GMT

Hi Philipp,

I am not sure if I had fully understood your requirement. Althoughyou can use Set Analysis to link BDate & QDate. Here is the example of theexpression...

SUM({$<BDate= QDate}>} OrderCounter)

You can also use vice-versa...

SUM({$<QDate= BDate}>} OrderCounter)

If this is not what you need, then please can you post the sample QV document with scrambled data?

Good luck!

Cheers- DV

Re: Problem with Dates

Tue, 09 Aug 2011 14:56:14 GMT

Hi Philipp,

The correct syntax is:

SUM({$<BDate= P(QDate)>} OrderCounter)

SUM({$<QDate= P(BDate)>} OrderCounter)

The syntax provided by DV will only work if only one value of QDATE (or BDATE for the second example) is selected.

Best regards,

Bert

Re: Problem with Dates

IAMDV — Tue, 09 Aug 2011 15:08:28 GMT

Oops! Bert is correct. You need use Indirect Analysis whereP represents the possible values and E represent the excluded values.

Thanks Bert for correcting me...

Re: Problem with Dates

johnw — Tue, 09 Aug 2011 21:04:01 GMT

You can create a single Date field linked to your base data by DateType. Use Date and DateType as dimensions, and you should get the correct sum(OrderCounter) for each. The attached is not using your data, but the approach should work on your data too. If you have trouble applying it, I'm sure I can create an example for your specific data.

Edit: In case there's confusion, the dates can be on the same table too. The example is just demonstrating that they don't have to be.

Edit: Attached an example that is closer to what you asked for. In this case, the two dates are on a single table, and we're using the DateType in the chart like I mentioned.