topic Re: Expression in chart pivot with different dates in QlikView

Expression in chart pivot with different dates

Mon, 02 Jul 2012 15:08:53 GMT

I have the following issue:

In my script I have 3 different dates:

1. KeyDate

2. Posting Date

3. Clearing Date

I use the KeyDate in my calender and users can then select a Year and a Month that is linked to the KeyDate.

What I would like to have in my expression in my pivot table is: when an user selects a year and a month, all items with a Posting Date before the last day of that month, but at the same time only the items with a Clearing Date after the last day of that month.

So if for instance March 2012 is selected, I would like to have all items with a Posting Date before 31-03-2012 and also have a Clearing Date after 31-03-2012.

Does anyone know how to do this??

Thx

Re: Expression in chart pivot with different dates

Jason_Michaelides — Mon, 02 Jul 2012 16:01:48 GMT

As your chart dimension, try:

IF([Posting Date] < MonthEnd(Max(KeyDate)) AND [Clearing Date] > MonthEnd(Max(KeyDate)),YourRequiredDimensionField)

You may be better served by using Set Analysis in your expressions, however.

Hope this helps,

Jason

Re: Expression in chart pivot with different dates

Tue, 03 Jul 2012 07:05:23 GMT

Hi Jason,

Thanks for your post!

The only problem is when a month and a year are selected, this selection is also applied to the Posting Date. I think this is caused by the fact that my KeyDate is based on the Posting Date.

So if March 2012 is selected I want to have all the items with a Posting Date before 31-03-2012, but now I only get the items with a Posting Date in March.

Do you know how to solve this??

Re: Expression in chart pivot with different dates

Jason_Michaelides — Tue, 03 Jul 2012 07:23:09 GMT

Your KeyDate should be in a separate table with every date between you min and max posting and clearing dates at least. You need to use an Autogenerate() while IterNo etc etc piece of script to generate your calendar. There are many examples of this script on the forum.

Jason

Re: Expression in chart pivot with different dates

Tue, 03 Jul 2012 11:13:47 GMT

Thx, I have now made an additonal loose calender and then it works!

Re: Expression in chart pivot with different dates

Tue, 03 Jul 2012 14:17:33 GMT

I still have one issue with the data based on the formula.

In the attached example I made a very simple pivot, but when I collapse the first Column called 'Item' then line '1' disappears.

Any idea why this happens?