topic Re: count with FirstSortedValue in QlikView

count with FirstSortedValue

Anonymous — Thu, 08 Dec 2016 14:14:07 GMT

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

Re: count with FirstSortedValue

sunny_talwar — Thu, 08 Dec 2016 14:40:34 GMT

PFA

Re: count with FirstSortedValue

Anonymous — Thu, 08 Dec 2016 14:55:26 GMT

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

Re: count with FirstSortedValue

sunny_talwar — Thu, 08 Dec 2016 14:58:50 GMT

What do you not understand?

Re: count with FirstSortedValue

Anonymous — Thu, 08 Dec 2016 15:02:41 GMT

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

Re: count with FirstSortedValue

sunny_talwar — Thu, 08 Dec 2016 15:06:02 GMT

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

Re: count with FirstSortedValue

Anonymous — Thu, 08 Dec 2016 15:08:53 GMT

Perfect! Many thanks for your help!

Re: count with FirstSortedValue

sunny_talwar — Thu, 08 Dec 2016 15:10:27 GMT

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