Conditional Set Analysis: Latest month in time selection or latest month in data

[#1]

Hi everybody,

I am facing the following problem. In my dashboard, I have a number of bar charts where the bars are with respect to product groups but not with respect to time (e.g., average monthly inventory value vs. product group). When filtering on a time period with more than one month, the bars are summed up for the respecitve months. Clearly, such a representation of the described chart does not make much sense.

Here is what I would like to achieve:

• When filtering on a time period with more than one month, the bar chart should be calculated ONLY with respect to the latest month in the time period;
• When no time filter is set at all (clear state), the bar chart should be calculated with respect to the latest month contained in the underlying data.

This should be attainable by means of set analysis.

Thanks in advance.

Best,

Tobias

[Oleg_Troyansky]

I'd recommend creating a variable that could hold the dynamic calculation of the filter, and then using that variable within a dollar-sign expansion within your set analysis. For example:

Variable vTimeFilter:

= IF( [selection condition], 'Month={[one formula]}', 'Month={[another formula]}')

Expression:

sum( {<$(vTimeFilter)>} Sales)

Having said that, it sounds like your condition is always the same - you always want the latest available month. If any time period is selected, then the latest available month is the one from the selected period, otherwise it's the latest available month in the data. If so, you don't even need a dynamic calculation. Just filter on the Month.

Be careful with the formatting, though - the typical Month field is dual, and the Set Analysis filter won't work with numbers. Use the numeric field MonthNumber, like this:

sum({<MonthNumber={$(=max(MonthNumber))}>}  Sales)

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[#3]

Hi Oleg,

Thanks for your quick reply. True, what I stated in two conditions boils actually down to a single condition.

I tested your solution and it works fine (but still requires a modification for my case). I implemented your solution as follows

sum({<year={$(=max(year))}, month={$(=Month(MakeDate(max(year),max(month))))}>}onhand_value)

• The first condition in set analysis filters on the maximum year:
  • year={$(=max(year))}

• The second condition filters on the maximum month and requires that the maximum month is converted to a string:
  • month={$(=Month(MakeDate(max(year),max(month))))}

But still one issue remains. In case the user makes no selection (clear state), the maximum month evaluates to December. However, data reaches only until Oct in the last year thereby resulting in an empty chart.

Thanks.

Cheers,

Tobias

[Oleg_Troyansky · Accepted solution]

Hi Tobias,

it's happening because of the separate selection of years and months, right? Individually, the latest Year is 2015 and the latest Month is Dec, however the latest year/month is October 2015. To avoid this problem, you should find a way to filter your Year/Month as a single field. So, everything I said in my original response about the field month, needs to be applied to the field MonthYear.

Furthermore, in your case (comparing inventory snapshots in different months) I'd recommend to place the corresponding field in the transactional table (Inventory) rather than in the Master Calendar, in order to enforce the condition - "the latest month in which I have inventory data", not just "the latest month in the calendar".

There are many ways to implement this logic, so I won't insult anyone's intelligence by spelling it out.

cheers,

Oleg Troyansky

[#5]

Hi Oleg,

I am sorry for my late reply. I implemented it exactly as you described.

1. When loading the inventory table, I create a field month_year from the date corresponding to each item
2. In the formula for the average inventory value, I use a set analyis expression of the following kind:
   <month_year={$(=max(month_year))}

Thanks,

Tobias