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

[#1]

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-week	Metric
1-1	40
1-2	38
1-3	43
1-4	41
2-1	42
2-2	36
2-3	38
2-4	42
3-1	39

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

[Anonymous]

Hi

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

[#3]

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

[#4]

Have you tried doing this in Expression?

You can easily pull off data, if you put this in an expression and in Set Analysis using 1 would make them unaffected from the selections made.

[Anonymous]

Hi Sergio

Now I know your script probaly looks a bit different but by using the function "LastValue" and "Group By"

I was able to pick the last value of each month in its own table. This field "LastMetric" that I created can be used

in your tables for showing or making calculations based on the last value for each month.

A_tmp:

LOAD

     Num(SubField([Month-week],'-',1)) As Month,        

     Metric

FROM [..\Data\External Data\test.txt] (txt, codepage is 1252, embedded labels, delimiter is ' ', msq) ;

LastMetric:

LOAD

     Month,

     LastValue(Metric) As LastMetric

     Resident A_tmp

Group By Month;

;