topic Re: How to create a calculated dimension with only month end dates for each month? in App Development

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

jblomqvist — Tue, 18 Sep 2018 12:12:44 GMT

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?

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

tush — Tue, 18 Sep 2018 12:20:24 GMT

Hi,

You can use monthend().

for more understanding go through below link:-

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/date-time-fun…‌

Thanks:-

Tushar

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

jonathandienst — Tue, 18 Sep 2018 12:29:49 GMT

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.