topic Re: Calling max value in set analysis in QlikView

Calling max value in set analysis

Thu, 15 Jan 2015 13:47:52 GMT

Hello,

I have loaded in a table with Fiscal Year and RowNo() in order to be able to call the Fiscal Year value in set analysis through the max RowNo. I have done this because I was unable to call the maximum Fiscal Year (format - 'FY14').

My formula should sum Volume for the most recent FY in the model (i.e. max row number). My set analysis is currently not working:

=sum({$<[FiscalYear2]={$(=max(RowNo()))}>}[Volume])

Similarly, I would like to do the same for RowNo - 1, to return the 2nd most recent FY.

The script to create the table is as follows:

[FY Count]:

LOAD distinct

FiscalYear2,

RowNo()

resident [FYCount]

;

Please can you advise?

Thanks,

Re: Calling max value in set analysis

ashwanin — Thu, 15 Jan 2015 14:47:16 GMT

for max FY use formula

=FirstSortedValue(FiscalYear2,-[RowNo()])

Also the original data seems to be different than the reflected one. Bz FY is a value derived from Dates not like this one.

Re: Calling max value in set analysis

Thu, 15 Jan 2015 14:50:35 GMT

Hi M,

what does your model look like, you are using FiscalYear2 in your set analysis, but from the code this looks like it might be a data island and not connected to your Volume field?

Also you are using the date field which is in the format 'FYxx' but trying to select '3' within it, so the set won't work.

What you want to do I believe is to create a variable which based on your max rowno() returns the corresponding FiscalYear2 value, you can then use that variable in your sum expression.

Please see attached, hope that helps

Joe

Re: Calling max value in set analysis

Thu, 15 Jan 2015 14:54:37 GMT

Hi Joe, yes that's exactly it!

I've amended my set analysis to call when RowNo() (which I've renamed to FYRow), which works:

=sum({$<FiscalYear2 = , [FYRow]={'2'}>}[Volume])

=sum({$<FiscalYear2 = , [FYRow]={'1'}>}[Volume])

Thanks for the help!

Re: Calling max value in set analysis

Thu, 15 Jan 2015 15:18:26 GMT

glad to help, sorry I thought you had those disconnected which is what I went with in my example.

One thing I would say is, you might want to avoid hardcoding the expressions like that, as it means next year you will need to adjust for the latest year, better to use a variable so it dynamically changes as time goes on

hope that helps

Joe