topic Re: Prob in set analysis in QlikView

Prob in set analysis

nikhilgarg — Mon, 06 Jul 2015 11:10:36 GMT

Hey,

I want max month for selected year.

I used following set analysis but not working:

=max({<[Fiscal Year] = {$(subfield(GetCurrentSelections(), ':', 2))}>}[Trade Month])

Where am i wrong?

thanks

Re: Prob in set analysis

swuehl — Mon, 06 Jul 2015 11:24:29 GMT

If you want to use a dollar sign expansion with an expression, you need a leading equal sign:

=max({<[Fiscal Year] = {$(=subfield(GetCurrentSelections(), ':', 2))}>}[Trade Month])

Re: Prob in set analysis

svenkita — Mon, 06 Jul 2015 11:25:15 GMT

Check the Text Box in the example attached

Re: Prob in set analysis

nikhilgarg — Mon, 06 Jul 2015 11:29:05 GMT

I tried this, but it didn't worked

Re: Prob in set analysis

nikhilgarg — Mon, 06 Jul 2015 11:29:21 GMT

Hey,

Thanks but i have persoal editn , can you tell me here only

Re: Prob in set analysis

swuehl — Mon, 06 Jul 2015 11:32:19 GMT

'It didn't worked' is not a very precise problem description.

What do you get when you put

=subfield(GetCurrentSelections(), ':', 2)

in a text box?

Since you are using GetCurrentSelections() I would only expect to get a year value back when the user is restricted to certain selections available.

In general, if you want the max Month for a certain selection, I would expect to get this by just something like:

=max( [Trade Month] )

Re: Prob in set analysis

svenkita — Mon, 06 Jul 2015 11:33:56 GMT

Following is the script I have used in

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/MM/YYYY';
SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET vDateMin = Num(MakeDate(2013,4,1));
LET vDateMax = Num(MakeDate(2016,3,11));

TempCalendar:
LOAD $(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Star_Calendar:
Load
TempDate As StarDate,
YearName(TempDate,0,4) As StarFiscalYear,
if(index(yearname(TempDate,0,4),'-')>0,
subfield(yearname(TempDate,0,4),'-',2),
year(TempDate)) As StarFiscalBudgetYear,
'Q'&
if (Num(Month(TempDate)) = 4 , 1,
if (Num(Month(TempDate)) = 5 , 1,
if (Num(Month(TempDate)) = 6 , 1,
if (Num(Month(TempDate)) = 7 , 2,
if (Num(Month(TempDate)) = 8 , 2,
if (Num(Month(TempDate)) = 9 , 2,
if (Num(Month(TempDate)) = 10 , 3,
if (Num(Month(TempDate)) = 11 , 3,
if (Num(Month(TempDate)) = 12 , 3,
if (Num(Month(TempDate)) = 1 , 4,
if (Num(Month(TempDate)) = 2 , 4,
if (Num(Month(TempDate)) = 3 , 4,
)))))))))))) As StarFiscalQuarter,
month(TempDate) As StarMonth,
Year(TempDate) As StarYear,
day(TempDate) As StarDay,
monthname(TempDate) As StarRollMonth,
week(TempDate) As StarWeek,
weekname(TempDate) As StarWeekName,
weekday(TempDate) As StarWeekDay,
if(weekday(TempDate)>=5,'WeekEnd','WeekDay') As StarWeekEnd,
QuarterName(TempDate,0,4) As StarQuarter
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
LET vDateMin = "";
LET vDateMax = "";
LET vDateToday = "";

After loading the scrip...

create a list box for StarFiscalYear

Create a textbox with

=Month(Max(StarDate))
& Chr(10) &
year(Max(StarDate))

on selection of a fiscal year on the listbox you will get the max Year and Month for the selected year

Re: Prob in set analysis

Kushal_Chawda — Mon, 06 Jul 2015 11:41:49 GMT

try,

max({<[Fiscal Year] = {"$(=subfield(GetCurrentSelections(), ':', 2))"}>}[Trade Month])