topic Re: Calculated dimension for max date of each month in QlikView

Calculated dimension for max date of each month

Fri, 18 Apr 2014 21:47:03 GMT

I'm trying to get a calculated dimension so that only one date for each month is shown in the dimension, how do I do this?

Based on the example above, I want my dimension values to be:

1/04/2014

12/31/2013

11/11/2013

9/30/2013

Any help would be greatly appreciated!

Re: Calculated dimension for max date of each month

tresB — Sat, 19 Apr 2014 06:10:09 GMT

Assuming that you have a Month field as well, you can try expression like: =Aggr(Max(Date), Month)

Re: Calculated dimension for max date of each month

MK_QSL — Sat, 19 Apr 2014 11:47:59 GMT

You can achieve this by two ways...

Create one more field in your script...

Month(DateField) as Month

1) Using Calculated Dimension

Aggr(Max(DateField),Month)

Expression

SUM(Sales) //or whatever as per your data model

2) Using Set Analysis

Dimension

Date

TIck Suppress When Value is NULL

Expression

Aggr(SUM({<Date = {"=Aggr(Max(Date),Month)"}>}Sales),Month)

Performance of 2nd method is much better for Large DataBase...

Re: Calculated dimension for max date of each month

Sat, 19 Apr 2014 18:38:59 GMT

Thank you, Manish!

Your 2nd method using set analysis is exactly what I was trying to figure out. It does not sum all the values for each month, but rather just displays a single value that corresponds to the single date that is displayed, which is what I wanted.