6 Replies Latest reply: Sep 2, 2013 5:30 AM by Friedrich Hofmann RSS

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

    Friedrich Hofmann

      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

        • Re: How to modify a month_dimension to the resp. last day?
          Stefan Wühl

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

           

          =FirstSortedValue(TotalNoEmployeesField ,-Date)

           

          replace TotalNoEmployeesField and Date with your appr. field names.

          • Re: How to modify a month_dimension to the resp. last day?
            Tresesco B

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

              • Re: How to modify a month_dimension to the resp. last day?
                Friedrich Hofmann

                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

                  • Re: How to modify a month_dimension to the resp. last day?
                    Friedrich Hofmann


                    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

                      • Re: How to modify a month_dimension to the resp. last day?
                        Stefan Wühl

                        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.


                          • Re: How to modify a month_dimension to the resp. last day?
                            Friedrich Hofmann

                            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