Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
klrameet
Contributor III
Contributor III

Issue with expression, not giving the expected results

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

A.PNG

 

Any help would be appreciated 

1 Solution

Accepted Solutions
petter
Partner
Partner

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 😮

View solution in original post

7 Replies
petter
Partner
Partner

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 😮

View solution in original post

klrameet
Contributor III
Contributor III
Author

thanks, it works perfect.

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

petter
Partner
Partner

- 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
Contributor III
Contributor III
Author

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

It doesn't matter. 🙂

klrameet
Contributor III
Contributor III
Author

@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 & -

AS.png

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

petter
Partner
Partner

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.