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: 
datanibbler
Champion
Champion

How to modify a month_dimension to the resp. last day?

Hi,

the scenario I have here is pretty much summed up in the title:

- I have a diagram where I have to display a number of different values rgd. our employees - well, up to now it's not a diagram. I have all the values displayed, but all in individual textboxes that react when the user selects a year and month.

- Some of the values only make sense per month, displaying them per day would be no good (like the employees leaving). Therefore I want to fix the dimension on month_level.

<=> Other values (like the total nr. of employees) are available per day, but it would make no sense to display them thus. Currently, I display all the values in individual textboxes, and those values I only display for the last date of any month.

- That is easy in textboxes, one month is selected by the user and I use a max() fct.

<=> In a diagram with the month as a dimension, the set_expression I have there would not avail me (max() would return only September)

The solution I can come up with would be in the master_calendar I have on scripting_level, using a MAKEDATE() fct. to create the 28th of every month (smallest common denominator of all months) and using that as a dimension.

Would there be an easier one?

Thanks a lot!

Best regards,

DataNibbler

6 Replies
swuehl
MVP
MVP

With month (or better YearMonth) as your dimension try something like

=FirstSortedValue(TotalNoEmployeesField ,-Date)

replace TotalNoEmployeesField and Date with your appr. field names.

tresesco
MVP
MVP

Not sure if i have understood you right. Try MonthEnd() function.

datanibbler
Champion
Champion
Author

Hi tresesco,

yes, that's what I want to do. I didn't yet know that fct., but makedate() works quite fine. I also have a field telling me how many days there are, so I don't even have to fix the 28th. I will try that fct, however.

@ swuehl

Thanks for your answer, too! I think however, that - since that last_day is the cutoff_day for several values - that it would be easier to create this in the master_calendar I link to and just use this as dimension.

Best regards,

DataNibbler

datanibbler
Champion
Champion
Author


Hi,

that is already pretty good. It works now when I remove the set_expression from my formula.

However, I would like - to make it look prettier - to not have the actual full date displayed on the x-axis, but only the monthname (Jan, Feb...) - and still have the value displayed as it is on the last day.

Is that possible?

Thanks a lot!

Best regards,

DataNibbler

swuehl
MVP
MVP

Well, looking at least at two different suggested approaches, it's quite hard to see which is working pretty good now from your answer.

You can get the month name from a date using Month() or Monthname(), or when using a single date just by using Date(Datefield,'MMM').

(when using multiple dates, note that only the formatting is changed to Month name, not the date values)

You can use these functions in a calculated dimension or create new fields in the script.

If you go for a script solution, you can also create a flag in the calender for the last day of a month

LOAD

     Date,

     Month,

     Day,

     Year,

     if( daystart(monthend(Date)) = Date, 1,0) as FlagMonthend,

     ...

Then use

=only( {<FlagMonthend = {1}>} TotalNoEmployeesField)

in your expression with Month as dimension.


datanibbler
Champion
Champion
Author

Hi,

that is a very good solution. Thanks for your answer!

There is, however, another difficulty that I have to discuss with HR first, namely the number of dimensions - I have two already because some values just cannot be displayed on the same scale, and I might need more - but it doesn't go together with the fact that two or three values should actually be displayed together.

I have an idea - it is just a value, so I might be able to display it on top of the column for the resp. month? That I'll read up in the book and if necessary, I will open a new thread to a avoid mixing issues.

Best regards,

DataNibbler