Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Search instead for
Did you mean:
Creator

## First Sorted Value with If Condition

Hi,

I am trying to use FirstSortedValue in an if condition but it gives me an error. Don't know what's going wrong. Everything looks correct according to the syntax. Can anyone help with the below expression and tell me what's going wrong?

FirstSortedValue(if(v1,

if(v2=1, sum(Field1 - Field2- Field3),

if(v3=1, sum(Field1 + Field4-Field3), sum(Field1-Field3)))

if(v2=1, sum(Field1 - Field2),

if(v3=1, sum(Field1 + Field4), sum(Field1)))

),-MonthNum)

Thanks in Advance!!!

1 Solution

Accepted Solutions
MVP

You are using an aggregation within an aggregation (Sum within FirstSortedValue). This require the use of Aggr() function.... You need something like this

FirstSortedValue(

Aggr(

if(v1,

if(v2=1, sum(Field1 - Field2- Field3),

if(v3=1, sum(Field1 + Field4-Field3), sum(Field1-Field3)))

if(v2=1, sum(Field1 - Field2),

if(v3=1, sum(Field1 + Field4), sum(Field1)))

),

ListofDimensionsSeparatedByCommas)

,-MonthNum)

4 Replies
MVP

You are using an aggregation within an aggregation (Sum within FirstSortedValue). This require the use of Aggr() function.... You need something like this

FirstSortedValue(

Aggr(

if(v1,

if(v2=1, sum(Field1 - Field2- Field3),

if(v3=1, sum(Field1 + Field4-Field3), sum(Field1-Field3)))

if(v2=1, sum(Field1 - Field2),

if(v3=1, sum(Field1 + Field4), sum(Field1)))

),

ListofDimensionsSeparatedByCommas)

,-MonthNum)

Creator
Author

Still doesn't work. I am trying to use it in an Adhoc report, so dimensions change according what the user picks up. So, how to get the fields in Aggr that the user selected? I tried using GetFieldSelections but that doesn't work.

Appreciate all the help.

MVP

Would you be able to share a sample? It might be easier to get this resolved that ways

Creator
Author

Hi Sunny,

I was using a wrong field in Aggregation and it wasn't working. Fixed it now and it works.... Thanks so much for your help..

Community Browser