Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

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

4 Replies
morris70
Not applicable

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

Hi

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

Not applicable

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

Not applicable

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

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.

morris70
Not applicable

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

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;

Capture.PNG.png;

Community Browser