Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

thomas_2583
New Contributor II

First Sorted Value not working

Hi,

I have the following logic below which looks correct syntax wise, but no data is returned when I would expect it too. 

=FirstSortedValue([brand], -aggr(
count({<[FY_Year]={'2019'}, [Status]={'New','Existing'}, [Months_To_Cancel]={'<4'}>}[Count_Flag])
/
count({<[FY_Year]={'2019'} >}[Count_Flag]) , [brand] ))

When I run the code as the sum itself without the Brand as a firstsortedvalue, I return a numeric value so that part of the calculation seems to work fine, but when I look at the firstsortedvalue, nothing is returned. Can anyone see from the logic used as to why this might be please?

1 Solution

Accepted Solutions

Re: First Sorted Value not working

May be you need set analysis within FirstSortedValue

=FirstSortedValue({<[FY_Year] = {'2019'}>}[brand], -Aggr(
  Count({<[FY_Year] = {'2019'}, [Status] = {'New', 'Existing'}, [Months_To_Cancel] = {'<4'}>}[Count_Flag])/
  Count({<[FY_Year] = {'2019'}>} [Count_Flag])
, [brand]))
6 Replies
asinha1991
Contributor III

Re: First Sorted Value not working

is '<4' a literal value or you want to show everything less than 4?

then do it like this

[Months_To_Cancel]={"<4"}

better create a table with Brand as dimension and count({<[FY_Year]={'2019'}, [Status]={'New','Existing'}, [Months_To_Cancel]={'<4'}>}[Count_Flag])
/
count({<[FY_Year]={'2019'} >}[Count_Flag]) as expression, try to see if it shows values

MVP
MVP

Re: First Sorted Value not working

Try like:

 

=FirstSortedValue( Aggr([brand],[brand]), -aggr(
count({<[FY_Year]={'2019'}, [Status]={'New','Existing'}, [Months_To_Cancel]={'<4'}>}[Count_Flag])
/
count({<[FY_Year]={'2019'} >}[Count_Flag]) , [brand] ))

Partner
Partner

Re: First Sorted Value not working

are you sure that you dont' have more brand with the same numeric value?
because in that case the function returns NULL.

you can also try:
=FirstSortedValue( distinct  [brand], -aggr(
count({<[FY_Year]={'2019'}, [Status]={'New','Existing'}, [Months_To_Cancel]={'<4'}>}[Count_Flag])
/
count({<[FY_Year]={'2019'} >}[Count_Flag]) , [brand] ))

thomas_2583
New Contributor II

Re: First Sorted Value not working

Thanks for your suggestions but sadly, neither return any result. 

I have tested and tried some new logic where it does return results, however, it's using the wrong date variable that I require but I do wonder if it's the variable 'FY_Year' that's the problem.

As you can see from the logic below, I have used 'Order_Date' and slightly tweaked the logic ('Order_Date' formatted as DD/MM/YYYY) is actually a Date variable whereas the variable 'FY_Year' is a General

=FirstSortedValue( [Brand] , -aggr(

Count(if(year([order_Date])=2019 and [Status]='New' or 'Existing' and [Months_To_Cancel] <4,[Count_Flag]))
/
Count( if(year([Order_Date])=2019 ,[Count_Flag] )), [Brand] ) )

 

Does anyone think it's the field type that's the issue?

Re: First Sorted Value not working

May be you need set analysis within FirstSortedValue

=FirstSortedValue({<[FY_Year] = {'2019'}>}[brand], -Aggr(
  Count({<[FY_Year] = {'2019'}, [Status] = {'New', 'Existing'}, [Months_To_Cancel] = {'<4'}>}[Count_Flag])/
  Count({<[FY_Year] = {'2019'}>} [Count_Flag])
, [brand]))
thomas_2583
New Contributor II

Re: First Sorted Value not working

Thank you, Sunny! Much appreciated.