4 Replies Latest reply: Nov 27, 2014 5:12 PM by Nils Morris RSS

    How to get max date's related result for each month dimension

      Dear fellow Qlikers,


      I've had the following problem many times while working on my reports:


      If I have a weekly reported metric and then want to show it's results in a monthly basis by taking the last week available of the month as the monthly result, how can I do that?


      Here's a practical example:


      For this example, I'd like to show the monthly results as follows:

      Month 1:   41

      Month 2:   42


      It seems like such a basic thing but I haven't found any viable solution to it yet. Using the set analysis max(date) would give me the overall maximum date, which I don't want. Is there any way of relating the set analysis formulas to the value of the dimension?


      Please help me with this, it will help me solve many issues .


      Thanks and best regards!!,


      Sergio Peschiera

        • Re: How to get max date's related result for each month dimension
          Nils Morris



          Just a quick question. In what format is your Month-Week field?

            • Re: How to get max date's related result for each month dimension

              Hello Nils,


              Thanks for answering this fast!


              Month is in the following format: mmm-FYXX (example: jun-FY14)

              Week      ""                    ""          : ww-FYXX (example: 44-FY14)


              Both formats are recognizable with the maxstring() function. The thing is that if I use {< calendarweekfy={"$(=maxstring(calendarweekfy))"} >} in set analysis, the function will return the maximum overall week. Hence, it only works for the last month.


              I've already figured a workaround by flagging the last week of the month in the calendar, but it doesn't always work, sometimes data is updated twice in a week so the last week of the month may contain two possible dates. What I'm looking for (for this case and several others I've been presented with a similar problem) is that the functions inside the set analysis are affected by the current dimensions they're related to. I'm not sure if Qlikview allows this but I think it'd be very helpful. Please let me know your comments, Nils. Thanks again for your help!