First Sorted Value with If Condition

[don_qlikview]

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

[sunny_talwar · Accepted solution]

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)

[don_qlikview]

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.

[sunny_talwar]

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

[don_qlikview]

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