Issue with expression, not giving the expected results

[klrameet]

Hi

I am trying to summerize the below table into the table above, by taking maximum of the cob date n version for a given report name for the rows with rank = 1 (in this case both rows are having rank 1, but it may not be the case always), for a single status value (F or C)

I used below expression for version n status column in summarized table above, but status for some reason is not getting populated.

Version:

=IF(EVENT_RANK =1,AGGR(MAX([FEED_REPORT_STATUS_LOG_QV.AXIOM_VERSION]),[FEED_REPORT_STATUS_LOG_QV.COB_DATE], [FEED_REPORT_STATUS_LOG_QV.REP_NAME]))

Status:

=IF(EVENT_RANK =1,Aggr(distinct([FEED_REPORT_STATUS_LOG_QV.STATUS]),[FEED_REPORT_STATUS_LOG_QV.COB_DATE],[FEED_REPORT_STATUS_LOG_QV.REP_NAME]))

Any help would be appreciated 

[petter · Accepted solution]

You use the DISTINCT qualifier as if it is a function. You will have to do something like this:

=IF(EVENT_RANK =1,Aggr( FirstSortedValue(DISTINCT [FEED_REPORT_STATUS_LOG_QV.STATUS],-Version),[FEED_REPORT_STATUS_LOG_QV.COB_DATE],[FEED_REPORT_STATUS_LOG_QV.REP_NAME]))

Please click LIKE if you find any answer helpful as this is the only way you can give "real credit" on this forum. 🙂
Only marking an answer as a solution will not give the receiver any credit - even though it looks nice on the screen 😮

[klrameet]

thanks, it works perfect.

Why r we using '-' in front of Version ?

[petter]

- will give descending sort instead of ascending. I guess you want to have the highest version number and that's why you have to have a minus.

[klrameet]

One more qn .. would the order/sequence of the fields matter ..  Should I list the fields/dimensions in particular order for the group by or sequence of the field don't matter ?

[petter]

It doesn't matter. 🙂

[klrameet]

@petter  ..  my expression using firstsortedvalue is returning two values, I even tried using IF to filter additional rows, but that just changed the output from getting C & F to F & -

Original:

=Aggr(FirstSortedValue(DISTINCT [FEED_REPORT_STATUS_LOG_QV.STATUS],-[FEED_REPORT_STATUS_LOG_QV.VERSION_NO]),[FEED_REPORT_STATUS_LOG_QV.COB_DATE],[FEED_REPORT_STATUS_LOG_QV.REP_NAME],EVENT_RANK)

Output: C & F

Modified:

=if(EVENT_RANK =1,Aggr(FirstSortedValue(DISTINCT [FEED_REPORT_STATUS_LOG_QV.STATUS],-[FEED_REPORT_STATUS_LOG_QV.VERSION_NO]),[FEED_REPORT_STATUS_LOG_QV.COB_DATE],[FEED_REPORT_STATUS_LOG_QV.REP_NAME],EVENT_RANK))

Output: F & -

What am i missing, I am expecting the output to be F only, corresponding to the first row.

[petter]

The Aggr() function should always be wrapped inside a real aggregation function because the Aggr() might return more than one value. It can return no value, one value or multiple values. When it returns multiple values most visualizations in Qlik can't show multiple values and therefore must be aggregated.