I don't know how it works and why?

[baylor2016]

Hi,

I used following expression in listbox as field expression

=aggr(firstSortedValue({$<Systolic-={"null()"}>}Systolic, -CONTACT_DATE), PAT_ID)

and used this in listbox expression to get the count.

=COUNT(aggr(firstSortedValue({$<Systolic-={"null()"}>}Systolic, -CONTACT_DATE), PAT_ID))

It works and it returns the last no-null Systolic value sorted by contact_date. What I do not know why it returns the count for Systolic which has two values (<140 and >=140).

I would like to add percentage by adding count(distinct {1} PAT_ID) in expression, It does not work. It always return 1.

Please let me know how aggr works here  and how to get percentage in the listbox.

Sample project is attached for your reference.

Thanks

Longmatch

[swuehl]

Your list box field expression does in fact look like

=aggr(firstSortedValue({$<Systolic22-={"null()"}>}Systolic22, -CONTACT_DATE), PAT_ID)

so you are using Systolic22 field.

Besides this, what do you want to achieve with Systolic22-={"null()"} field modifier? There is no value null(), but there is one called NULL:

=aggr(firstSortedValue({$<Systolic22-={'NULL'}>}Systolic22, -CONTACT_DATE), PAT_ID)

If you want to calculate the percentage, try

.../count(TOTAL distinct {1} PAT_ID)

[baylor2016]

How does aggr(firstSortedValue function work in the field expression? I made it work, but I do not know why it works this way. Could you explain? Thanks

[Oleg_Troyansky]

In addition to everything Stephan had explained, let me add a little explanation of how it works and why do you get counts for both >140 and <140... And also fix another problem in your calculation.

AGGR calculates your aggregated values for each value of your AGGR dimensions. In your case the First Sorted Value of the field is aggregated per Patient. Then, the aggregated results are aggregated again within the function count().

For some Patients, the most recent value of Systolic22 was >140 and for some other Patients, the most recent value was <140. This is why you are getting both counts.

However, I think your results are incorrect. You can validate them by selecting small numbers of both values and counting them manually. The reason for that is this... You violated the rule that I call "The Third Rule of AGGR()". When using AGGR in charts (your list box expressions are equivalent to a chart), the AGGR dimensions MUST be as detailed or more detailed than the Chart dimensions. In your case, you have Systolic22 as the implied Dimension (the List Box field), but your AGGR doesn't have Systolic22 as a dimension, and as a result, the counts are wrong.

I think the corrected formula should look like this:

=aggr(firstSortedValue({$<Systolic22-={'NULL'}>}Systolic22, -CONTACT_DATE), PAT_ID, Systolic22)

Learn more about AGGR() and other advanced QlikView techniques from my book QlikView Your Business, or at the upcoming session of the Masters Summit for Qlik in Milan!

cheers,

Oleg Troyansky

[baylor2016]

When replace null() with NULL in the expression, the Null disappears from the list.

[swuehl]

That's what you are telling QV to do using the minus operator, right?

Systolic22-={'NULL'}

[baylor2016]

Actually, I need the number of nulls in the listbox in order to show the whole thing in percentage.

[Oleg_Troyansky]

When you add Expressions to List Boxes, they work just like an Expression in a Straight Table with your List Box Filed acting as an implied Dimension. So, the function FirstSortedValue, sorted by negative date, will return the latest value of the field by Patient, subject to your Set Analysis conditions.

Keep in mind that your FirstSortedValue is enclosed in the AGGR() function. In this case, it works like an expression within a Straight Table with the AGGR() dimensions acting like the Chart dimensions.

cheers,

Oleg Troyansky

[Oleg_Troyansky · Accepted solution]

In this case, just remove the filter that was excluding NULL values from the calculation. If your intention was to eliminate empty (Null()) values, the syntax is:

Systolic22={"=len(trim(Systolic22))>0"}

cheers,

Oleg