topic Re: set analysis using start of month in QlikView

set analysis using start of month

Thu, 20 Sep 2012 15:45:54 GMT

hi folks

After trying this several different ways I think im losing the plot with set analysis and dates

if i use

sum({1< Date = {'01/09/2012'}, RecordType = {'Opening Balance'},Tabletype ={'Total'} >}Members)

it brings back the answer i want.. brilliant

all im trying to do is substitute the '01/09/2012' for the 1st day of the month

e.g

monthstart(Today())

any clues on putting the above in the set analysis as

sum({1< Date = {'monthstart(Today())'}, RecordType = {'Opening Balance'},Tabletype ={'Total'} >}Members)

fails to give anything back

Date is part of a master calendar which is loaded via

MasterCalendar:

Load Id,

date(Date, 'DD/MM/YYYY') as Date

,Year,Month,MonthName,DayOfMonth,DayOfWeek,DayName,Quarter,Week,DayOfYear;

SQL SELECT *

FROM DimDate

thank you

Re: set analysis using start of month

swuehl — Thu, 20 Sep 2012 16:00:05 GMT

Try

=sum(

{1< Date = {'$(=Date(monthstart(Today()),'DD/MM/YYYY') )'}, RecordType = {'Opening Balance'},Tabletype ={'Total'} >}

Members)

Re: set analysis using start of month

Fri, 21 Sep 2012 08:16:52 GMT

Thank you Swuehl, there appears to be a lot of underlining red in the syntax, suggesting it may be wrong but it is giving the correct answer so thanks again