topic Re: Firstsortedvalue in QlikView

Firstsortedvalue

Mon, 26 Nov 2012 21:53:53 GMT

Need help figuring out why this doesnt aggregate correctly. Trying to display, in a text box, the current year growth of a partner (VAR Reporting Name) based on this year to date as compared to the same year last year thru the SAME date last year.

=FirstSortedValue(

num(aggr(

(sum({<[Calendar Year]=,[VAR Reporting Name]= {'$(vSolutionProviderName)'}>} if (InYear ([Invoice Date], vMaxInvDateFiltered, 0) and ([Invoice Date]) <= vMaxInvDateFiltered , [Total Sales])) /

sum({<[Calendar Year]=,[VAR Reporting Name]= {'$(vSolutionProviderName)'}>}if (InYear ([Invoice Date], vMaxInvDateFiltered, -1) and ([Invoice Date]) <= vMaxInvDateFiltered-365, [Total Sales]))

),[IDC Vertical Market]),'#,##0.0%')

,

-aggr(

sum({<[Calendar Year]=,[VAR Reporting Name]= {'$(vSolutionProviderName)'}>} if (InYear ([Invoice Date], vMaxInvDateFiltered, 0) and ([Invoice Date]) <= vMaxInvDateFiltered , [Total Sales])) /

sum({<[Calendar Year]=,[VAR Reporting Name]= {'$(vSolutionProviderName)'}>}if (InYear ([Invoice Date], vMaxInvDateFiltered, -1) and ([Invoice Date]) <= vMaxInvDateFiltered-365, [Total Sales]))

,[IDC Vertical Market]))

Re: Firstsortedvalue

vgutkovsky — Tue, 27 Nov 2012 04:27:51 GMT

I'm not exactly sure why you're using firstsortedvalue--that function does something totally different than what you're trying to do. The easiest way to do what you want is to create a new variable:

vLastYearYTD = addyears(vMaxInvDateFiltered,-1)

Then use this new variable in your expression as follows:

(sum({<[Calendar Year]=,[VAR Reporting Name]= {'$(vSolutionProviderName)'},[Invoice Date]={"<=$(vMaxInvDateFiltered)"}>} [Total Sales])

(sum({<[Calendar Year]=,[VAR Reporting Name]= {'$(vSolutionProviderName)'},[Invoice Date]={"<=$(vLastYearYTD)"}>} [Total Sales])

I don't think you need to aggr() unless there are some business rules I am not aware of. If you need to keep the aggr, you need to either surround it by an aggregation expression or make sure it return a single value (because it's in a text box).

Regards,

Vlad

Re: Firstsortedvalue

Tue, 27 Nov 2012 15:37:40 GMT

Thank
you Vlad

I am
aggr() because i want the name of the customer and the value for the customer with the top sales growth for the given category in each text box. So if Customer ABC has the highest YOY growth based on the same time this year compared to the same time last year, over all customers for the given Solution Provider , I want to display that name in one text box and the value of the growth in the other text box. Thus, FirstSOrtedValue() function. DOes that help? SOrry I wasnt clear.

It works for the top sales $ value, it is the growth formula that is not working correctly.

In this case I want the customer name for that
customer with the highest growth

Re: Firstsortedvalue

Tue, 27 Nov 2012 15:41:37 GMT

I wrote this incorrectly:

WRONG:

"Need help figuring out why this doesnt aggregate correctly. Trying to display, in a text box, the current year growth of a partner (VAR Reporting Name) based on this year to date as compared to the same year last year thru the SAME date last year."

"Need to display the largest growth rate of all the growth rates for [IDC Vertical Market]. I need to display the name of the [IDC Vertical Market] with the highest growth rate in another text box." I am doing the same for the Top End Customer, Top State, Top Brand, etc....

Re: Firstsortedvalue

Tue, 27 Nov 2012 15:41:56 GMT

second paragraph is correct.

Re: Firstsortedvalue

vgutkovsky — Tue, 27 Nov 2012 18:55:38 GMT

Try this to display the highest rate (using the variable I created above):

Create a variable called vMaxMarketRate:

=max(aggr(

(sum({<[Calendar Year]=,[VAR Reporting Name]= {'$(vSolutionProviderName)'},[Invoice Date]={"<=$(vMaxInvDateFiltered)"}>} [Total Sales])

(sum({<[Calendar Year]=,[VAR Reporting Name]= {'$(vSolutionProviderName)'},[Invoice Date]={"<=$(vLastYearYTD)"}>} [Total Sales])

,[IDC Vertical Market]))

You can just enter vMaxMarketRate into the text box. The following should display the name of the market with the highest rate:

maxstring(aggr(

if(

(sum({<[Calendar Year]=,[VAR Reporting Name]= {'$(vSolutionProviderName)'},[Invoice Date]={"<=$(vMaxInvDateFiltered)"}>} [Total Sales])

(sum({<[Calendar Year]=,[VAR Reporting Name]= {'$(vSolutionProviderName)'},[Invoice Date]={"<=$(vLastYearYTD)"}>} [Total Sales])=vMaxMarketRate,[IDC Vertical Market])

,[IDC Vertical Market]))