Aggr issue on set analysis

[#1]

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?

[Anonymous]

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!!!

[#3]

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.

[#4]

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...

[Anonymous]

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!

[sunny_talwar]

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))

[cesaraccardi]

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

[#8]

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.

[#9]

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.

[sunny_talwar]

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))