topic Re: Aggr issue on set analysis in QlikView

Aggr issue on set analysis

Wed, 25 Nov 2020 16:16:04 GMT

I have a table as below:

Equipment Type Date

0001 A 02/01/2014

0002 A 02/01/2014

A 03/01/2014

0004 A 03/01/2014

Equipment can be repeated, so for an equipment more than one row can exists in the table.

Based on the above table I am trying to count the number of rows that Date field is between a date selected by user (Selected_Date). I want to do this calculation for each equipment and finally I want to obtain the total sum of all equipment. Note that third row has Equipment field empty so I want to discard this row from Aggr function. so I indicate a set analysis within aggr function. I have below expression:

=Sum(

Aggr(

{$<[Equipment]-={' ','','Null'}>}

Count({$<[Type] = {'A'},Date={'>=$(=Min(Selected_Date))<=$(=Max(Selected_Date))'}>}Date)

, Equipment

)

If for example user selects date range [02/01/2014, 03/01/2014], so Min(Selected_Date) is 02/01/2014 and Max(Selected_Date) is 03/01/2014, I am expecting to obtain 3 as a result from the above table but instead I am getting 4. It seems like Aggr is taken into account third row which has Equipment field empty. I want to discard this row. How can I do this?

Re: Aggr issue on set analysis

Anonymous — Wed, 31 Aug 2016 07:44:30 GMT

Hi Tony,

Change this {$<[Equipment]-={' ','','Null'}>} by this {$<[Equipment]={'*'}>}

Other option is:

=Sum(

Aggr(

If(Not(IsNull(Equipment)),

Count({$<[Type] = {'A'},Date={'>=$(=Min(Selected_Date))<=$(=Max(Selected_Date))'}>}Date)

)

, Equipment

)

Regards!!!

Re: Aggr issue on set analysis

Wed, 31 Aug 2016 08:11:57 GMT

It is not working. Both solutions are returning 4 instead of 3. Aggr is not taken into account neither the set analysis nor the if conditional.

Re: Aggr issue on set analysis

Wed, 31 Aug 2016 08:19:22 GMT

Using below is working:

=Sum(

Aggr(

if(Len(Trim(Equipment))>0,

Count({$<[Type] = {'A'},Date={'>=$(=Min(Selected_Date))<=$(=Max(Selected_Date))'}>}Date)

))

, Equipment

)

Why is Aggr function is taken into account by default blanks or nulls? I had understood that it was ignoring blanks or nulls...

Re: Aggr issue on set analysis

Anonymous — Wed, 31 Aug 2016 08:25:10 GMT

Hi Tony,

Aggr function takes blank as a value, with {$<[Equipment]={'*'}>} you will ignore Nulls, but not blanks. Blank values aren't null. I think that is better to modify blank values by null values on script, on non description fields

Regards!

Re: Aggr issue on set analysis

sunny_talwar — Wed, 31 Aug 2016 09:10:10 GMT

I suggest fixing the Equipment column in the script:

LOAD If(Len(Trim(Equipment)) > 0, Equipment) as Equipment,

Type,

Date

FROM ....

and then may be this:

=Sum({$<[Equipment] = {'*'}>} Aggr(Count({$<[Type] = {'A'}, Date={'>=$(=Min(Selected_Date))<=$(=Max(Selected_Date))'}[Equipment] = {'*'}>}Date), Equipment))

Re: Aggr issue on set analysis

cesaraccardi — Wed, 31 Aug 2016 09:13:22 GMT

Hi Tony,

Do you really need the aggr() in this case? I mean if you are only interested in counting the number of equipments can't you just do that using count distinct?

Count(distinct {$<[Type] = {'A'},Date={'>=$(=Min(Selected_Date))<=$(=Max(Selected_Date))'}>} Equipment)

Then to filter out the blanks you could add the set modifier below (assuming that Equipment id's have a numeric representation):

Count(distinct {$<[Type] = {'A'},Date={'>=$(=Min(Selected_Date))<=$(=Max(Selected_Date))'},Equipment={">0"}>} Equipment)

Regards

Re: Aggr issue on set analysis

Thu, 01 Sep 2016 00:13:51 GMT

For each equipment I need to obtain the number of rows which date is in the date range selected. This is the reason why I need Aggr and not only Count.

Re: Aggr issue on set analysis

Thu, 01 Sep 2016 00:19:49 GMT

But filtering Equipment in LOAD in the script, empty Equipments are not taken into account and not loaded. In my case I need to load empty Equipment because I need them for other calculations for other purposes.

Re: Aggr issue on set analysis

sunny_talwar — Thu, 01 Sep 2016 00:22:07 GMT

What about bring it twice?

LOAD If(Len(Trim(Equipment)) > 0, Equipment) as Equipment,

Equipment as Equipment4Calc,

Type,

Date

FROM ....

and then may be this:

=Sum({$<[Equipment] = {'*'}>} Aggr(Count({$<[Type] = {'A'}, Date={'>=$(=Min(Selected_Date))<=$(=Max(Selected_Date))'}[Equipment] = {'*'}>}Date), Equipment))