topic Re: Using MIN function in set analysis in QlikView

Using MIN function in set analysis

Mon, 02 Jun 2014 13:22:20 GMT

Hi all,

Currently I want to make a pivot table to show various numbers for the dimension.

There are 2 dimensions, Property and Counter. I want to know the amount received from the counters and sum them up to give the value for the Property.

That is simple, but what I want to do is to have only those counters with the least Start Date per Property.

I tried to make that work with MIN function in set analysis.

However, the only result shown is the counter with overall the least Start Date in the table.

While for the others Property, if there is no counter with Start Date on the MIN(Start Date) in the overall table, no values would be shown.

I had attached the qvw to illustrate the situation. The expression I am using is :

SUM({<StartDate={"$(=date(min(aggr(min(StartDate),Property))))"}>}Amount) (4th expression in the table)

Is that the limitation for the MIN() function in set analysis? Is there any other method to make this work?

Thanks in advance for the help.

Re: Using MIN function in set analysis

maxgro — Mon, 02 Jun 2014 13:28:55 GMT

qvw is missing

Re: Using MIN function in set analysis

Mon, 02 Jun 2014 13:29:59 GMT

Sorry all.

Here is the qvw for your reference.

Re: Using MIN function in set analysis

stigchel — Mon, 02 Jun 2014 13:52:05 GMT

The dollar sign expansion is only evaluated once, so not for every value in the dimension. You can see the result of that in your expression header

=SUM({<StartDate={"1/1/2011"}>}Amount)

This is used for every value in your dimension.

One solution would be to load a table (in script) with only the min date values of your sheet1 table.