Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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

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

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

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

View solution in original post

9 Replies
Highlighted
Partner
Partner

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}

Highlighted
Specialist II
Specialist II

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

View solution in original post

Highlighted
Partner
Partner

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!

Highlighted
Specialist
Specialist

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

Highlighted
Specialist II
Specialist II

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

Highlighted
Specialist
Specialist

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

Highlighted
Not applicable

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

Highlighted
Creator III
Creator III

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

Highlighted
Contributor II
Contributor II

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;