topic First Sorted Value with If Condition in QlikView

First Sorted Value with If Condition

don_qlikview — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: First Sorted Value with If Condition

sunny_talwar — Tue, 25 Jul 2017 14:17:10 GMT

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)

Re: First Sorted Value with If Condition

don_qlikview — Tue, 25 Jul 2017 14:32:32 GMT

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.

Re: First Sorted Value with If Condition

sunny_talwar — Tue, 25 Jul 2017 14:38:11 GMT

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

Re: First Sorted Value with If Condition

don_qlikview — Tue, 25 Jul 2017 15:20:18 GMT

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