How to create a calculated dimension with only month end dates for each month?

[jblomqvist]

Hello,

I have a Date field which has 30,000 dates.

How can I create a Calculated Dimension that gives me the last date of each month from those dates?

[tush]

Hi,

     You can use monthend().

for more understanding go through below link:-

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFun...‌

Thanks:-

Tushar

[jonathandienst]

A better way to do this is to create a Month End field in a master calendar. Calculated dimensions can perform poorly on large data sets, and is should be easy enough to create the field in the load script.

How do you have 30,000 dates? Is this field a timestamp with time as well as date. You may want to split that field into two - a date field and a rounded time field:

Date(Floor(TimestampField)) as DateField,                         // one entry per date

Time(Round(Frac(TimestampField), 1/1440)) as TimeField,   // time rounded to the nearest minute (60*24 values)

Date(Floor(MonthEnd(TimestampField)), 'YYYY-MM') as MonthEndField,   // per your requirement

This lowers the cardinality of the date related fields which should improve performance as a whole.