topic Re: Using Set Analysis for Period Filter in QlikView

Using Set Analysis for Period Filter

Tue, 14 Apr 2015 08:33:47 GMT

I am facing a particular issue in pivot table for Inventory analysis.

In Dimension we have Quarter-Year

So, When I want to calculate Beginning of Period Inventory for Q1 of 2014, it should take value of Jan 14 and not summation of Jan Feb and March.

I have tried to do that using set analysis but it’s not working:

Expression:

Sum({<Month= {$(=min(Month))}>}Inventory)

Dimension: Quarter – Year

I am not looking at solution using multiple if conditions as the expression would become too long and complex, the expression above is just a sample of bigger set.

Any help would be much appreciated………………………

Re: Using Set Analysis for Period Filter

marcus_sommer — Tue, 14 Apr 2015 13:38:50 GMT

Probably your expression failed because either Month isn't numeric or if it's a dual-value the format isn't excactly the same. This meant a min(String) won't work and matching from Month values like 'Jan', 'Feb', ... won't match with numeric Month values like 1, 2, ... which a min(Month) will be returned.

I suggest you used for all date- and period-matchings numeric fields and extend your datamodel to these fields if they don't exists and then:

Sum({<MonthNum = {"$(=min(MonthNUM))"}>}Inventory)

- Marcus

Re: Using Set Analysis for Period Filter

Tue, 14 Apr 2015 14:24:21 GMT

Can you post your application?