count with FirstSortedValue

[Anonymous]

Hello Community,

currently I stuck in a question witch is more ore less already discussed within the forum.

But unfortunately I don't find the right solution or maybe I don't understand to use the already provided solutions with my data?

I try to calculate the number of unique values and the percentage for my data.

My data includes two tables, one table with unique customers and one with different datasets to each customer with different values.

Customer:

%ID, Name, AREA, Criteria
1, ABC, 01, B
2, BCD, 01, A
...

DATA:
ID_Data, %ID, DATE, value1, value2
1, 1, 25.11.2016, yes, no
2, 2, 30.10.2016, no, yes
3, 2, 02.11.2016, yes, yes
...

The both tables are linked by the Key %ID .

I have already a table in which both tables are linked and the output is a list of customers with the last value and date for each value.

I tried to calculate the number of values from the data table for each customer witch has the criteria A or B.
And in a second formula I try to calculate the percentage of the values. --> Here I'm not able to calculate the complete sum of A and B per value.

I hope, I could explain my questions understandable?

Many thanks in advance for your support!

Best regards,

Claus

[sunny_talwar · Accepted solution]

PFA

[Anonymous]

Many thanks again Sunny!

The calculation of the percentage works by this formula:

=Sum(Aggr(If(FirstSortedValue({<value1 = {"=Len(Trim(value1)) > 0"},  Criteria = {'A', 'B'}>} value1, -DATE) = Dim, 1, 0), AREA, Name, Criteria, Dim))
/
Sum(Aggr(If(FirstSortedValue({<value1 = {"=Len(Trim(value1)) > 0"},  Criteria = {'A', 'B'}>} value1, -DATE) = Dim, 1, 1 ), AREA, Name, Criteria, Dim))*2

I'm not sure if I understand this correct?

Best regards,

Claus

[sunny_talwar]

What do you not understand?

[Anonymous]

The DIM construct and the limitation by = DIM, 1,1  and *2 at the end in my percentage calculation.

I'm not sure if there is a smarter solution?

Best regards,

Claus

[sunny_talwar]

Here is the smarter solution:

=Sum(Aggr(If(FirstSortedValue({<value1 = {"=Len(Trim(value1)) > 0"},  Criteria = {'A', 'B'}>} value1, -DATE) = Dim, 1, 0), AREA, Name, Criteria, Dim))

/

Sum(TOTAL Aggr(If(FirstSortedValue({<value1 = {"=Len(Trim(value1)) > 0"},  Criteria = {'A', 'B'}>} value1, -DATE) = Dim, 1, 0), AREA, Name, Criteria, Dim))

[Anonymous]

Perfect! Many thanks for your help!

[sunny_talwar]

No problem my friend, I am glad I have been able to help you