Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression in chart pivot with different dates

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

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

View solution in original post

5 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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

Not applicable
Author

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?