topic set analysis ... Month =< getfieldselection(Month)? in QlikView

set analysis ... Month =< getfieldselection(Month)?

Anonymous — Thu, 24 Jun 2010 09:29:21 GMT

Hi All,

I'm trying to do something with set analysis ... but can't seem to find the right solution

I want to set the Month to something like Month <= selected Month.

Did try the following, but somehow the month is not selected correctly.


sum( {< Month = {"<$(=GetFieldSelections(Month))"} >} [Amount])

Could this have to do with the GetFieldSelections returning a text while my month is expecting a number??

Anyone got suggestions??

thx

Anita

AW:set analysis ... Month =< getfieldselection(Month)?

brenner_martina — Thu, 24 Jun 2010 09:39:34 GMT

definitly yes, Amount is a field or a column of the chart? Define first a variable

vMonth =(Month)

than the right entry is:

Sum({$< Month = {"<=$(vMonth)"} Amount}

set analysis ... Month =< getfieldselection(Month)?

sparur — Thu, 24 Jun 2010 09:42:10 GMT

Sum({$< Month = {"<=$(=max(Month))"} Amount)

AW:set analysis ... Month =< getfieldselection(Month)?

brenner_martina — Thu, 24 Jun 2010 09:42:53 GMT

yes, is Amount a field or a column of the chart?

Define first a variable

vMonth =(Month)

sum( {$< Month = {"<=$(vMonth)"} >} Amount)

this shows only the values of the selected year, what about all other years? Then you have to use the function Num(Monthname(Datefield)) for an unique monthnumber!

AW:set analysis ... Month =< getfieldselection(Month)?

Anonymous — Thu, 24 Jun 2010 09:49:09 GMT

I need the selected month (this is not per definition the max month ... ) ...

I have a table showing the amounts etc for the month, with one column for the selected year until and with this month ... so the higher months should not be counted.

I have a selection box for month ... and this selection i want to use for the set analysis.

Problem I guess there is is that the value the GetFieldSelections returns for the month selection is a text containing the month name instead of the month number which I need.

regards

AW:set analysis ... Month =< getfieldselection(Month)?

sparur — Thu, 24 Jun 2010 09:52:15 GMT

when you select June in Month field then max(Month) will return 6

when you select some values in Month (for example March - August) - max(Month) will return 8

AW:set analysis ... Month =< getfieldselection(Month)?

Anonymous — Thu, 24 Jun 2010 10:00:39 GMT

Thanks this works ... 😉 Forgot the simple things ... was searching for a to difficult solution here!!

AW:set analysis ... Month =< getfieldselection(Month)?

Tue, 16 Aug 2011 21:43:01 GMT

How can I get this to say July instead of 7?

Re: AW:set analysis ... Month =< getfieldselection(Month)?

sridhar240784 — Wed, 17 Aug 2011 03:12:06 GMT

Hi,

To get the July instead of 7, you may need to extract the Month from max of date field.

You expression should be as follows.

Sum({$< Month = {"<=$(=Month(max(DateField)))"} Amount)

Hope this helps you.

-Sridhar

Re: set analysis ... Month =< getfieldselection(Month)?

joaquin66 — Sat, 03 Feb 2018 03:38:39 GMT

Hi Anatoly,

I am trying to use below expression but is not working, the result am getting is the sum of all the months, like GLMonth = {blank}

GLMonth field is all the months of the calendar.

Sum({$<Transactionsource={'Open Orders'}, GLMonth = {"<=$(=max(GLMonth)+1)"}>} [Ext Price-F])

script:

LOAD
TempDate AS [G/L Fiscal Date],
week(TempDate) As GLWeek,
Year(TempDate) As GLYear,
Month(TempDate) As GLMonth,
// Day(TempDate) As GLDay,
YeartoDate(TempDate)*-1 as GLCurYTDFlag,
YeartoDate(TempDate,-1)*-1 as GLLastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as GLRC12,
date(monthstart(TempDate), 'MMM-YYYY') as GLMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as GLQuarter,
Year(TempDate) & '-' & ApplyMap('QuartersMap', month(TempDate), Null()) as GLYearQtr,
date(monthstart(TempDate), 'YYYY-MMM') as GLYearMonth,
WeekYear(TempDate) & '-' & Week(weekstart(TempDate)) as GLYearWeek,
Year(TempDate) & '-' & Day(TempDate) as GLYearDay,
WeekDay(TempDate) as GLWeekDay,
Year(TempDate) & Month(TempDate) as ROPDateKey
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;