Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calling max value in set analysis

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,

M

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

4 Replies
ashwanin
Specialist
Specialist

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.

Not applicable
Author

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

Not applicable
Author

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!


M

Not applicable
Author

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