Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi
Just a quick question. In what format is your Month-Week field?
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
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.
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;
;