Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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)
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)
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.
Would you be able to share a sample? It might be easier to get this resolved that ways
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..