topic Formula in Set Analysis in QlikView

Formula in Set Analysis

lockematthewp — Tue, 12 Mar 2019 17:25:55 GMT

=Num(Sum(
{$<[Data Type(B)] = {'eForecast Hrs System'}>
*$<[Data Classification(B)] = {MaxString([Data Classification(B)])}> }
Hours),'#,##0.00')

I need help with this expression. Specifically the second part with the Data Classification. I intend for the formula to choose the latest value for Data Classification (F1, F2, etc...). This part of the formula works in a text box but I am unable to find the right format for the set analysis.

Thanks,

Matt

Re: Formula in Set Analysis

jwjackso — Tue, 12 Mar 2019 17:57:49 GMT

=Num(Sum(
{$<[Data Type(B)] = {'eForecast Hrs System'}
,[Data Classification(B)] = {"=MaxString([Data Classification(B)])"}> }
Hours),'#,##0.00')

Re: Formula in Set Analysis

lockematthewp — Tue, 12 Mar 2019 19:37:20 GMT

This format is valid but it is always returning 0. It works if I manually type in a value instead of the =MaxString.

Re: Formula in Set Analysis

bharathadde — Tue, 12 Mar 2019 21:18:48 GMT

Try this

=Num(Sum(
{$<[Data Type(B)] = {'eForecast Hrs System'}
,[Data Classification(B)] = {'$(=MaxString([Data Classification(B)]))'}> }
Hours),'#,##0.00')

The only change I did is including $ sign before maxstring. In qlikview if you need to include a formula in set analysis we need to use $(=Formula) otherwise it will just take it as a String.

"=MaxString([Data Classification(B)])" taken as string =MaxString([Data Classification(B)])

'$(=MaxString([Data Classification(B)]))' gives you max value