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:

      Month-weekMetric
      1-140
      1-238
      1-343
      1-441
      2-142
      2-236
      2-338
      2-442
      3-139


      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

          Hi

           

          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!

               

              Sergio