Showing the value for a specific month, when dimension is also month

Tue, 11 Oct 2016 09:32:23 GMT

Hi,

I'm trying to use set analysis to get a value for a month according to certain filtering. But I can't get this to work the way I need it to.

Here's a simplified version of my calendar:

Months:
LOAD * INLINE [Month, OfficialReportingPeriod
1, 1
2, 1
3, 1
4, 1
5, 1
6, 1
7, 1
8, 1
9, 0
10, 0
12, 0];

And here's a simplified version of my data:

Values:

LOAD * INLINE [Month, Value

1, 400

2, 450

3, 430

4, 420

5, 510

6, 580

7, 560

8, 490

9, 390

10, 470,

11, 510

12, 520];

I'm showing values by month ( dimension : month, expr1 : sum(Value) ), but I want a second expr which would always show only the value of latest month where OfficialReportingPeriod = 1. Currently I'm trying something like this:

=sum(TOTAL
  {$<Month = {"$(=max(TOTAL {$< OfficialTimePeriod = {'1'} >} Month))"}>}
  Value)

But this returns the value of month 12 (520), not month 8 (490).

Re: Showing the value for a specific month, when dimension is also month

sunny_talwar — Tue, 11 Oct 2016 10:21:46 GMT

Try this:

=FirstSortedValue(TOTAL {$<OfficialReportingPeriod = {1}>} Aggr(Sum({$<OfficialReportingPeriod = {1}>} Value), Month), -Month)

topic Showing the value for a specific month, when dimension is also month in QlikView

Showing the value for a specific month, when dimension is also month

Re: Showing the value for a specific month, when dimension is also month