Set Expression with Dates

[#1]

I don't think this should be too hard but having trouble getting the set expression to work:

I am trying to sum on a field called demand where the demand occurs in the current month + 1

For examples - I have a series of demand entries - time with different dates attached in the future.  I want to pull in the demand where the date is in March (Feb + 1). 

My fields are:

- DemandMonth - this is the month where the demand occurs

- Demand - this is the amount of hours

I have a master calendar that is linked to the date and creates the demandmonth field

[martin59]

Hi,

If your field DemandMonth is numeric, I suggest you to do that :

Data:

LOAD ID,

     ...,

     MakeDate(Year(Today()), DemandMonth) as DemandDate,

     Demand

FROM Data.qvd;

Calendar:

LOAD Distinct DemandDate,

     MonthName(DemandDate) as DemandYearMonth,

     Month(DemandDate) as DemandMonth

RESIDENT Data;

After that, you have to use something like this in design part :

Sum({<DemandMonth={"$(=MonthName(Today(),1))"}>} Demand)

Hope that helps you

Martin Favier

[martin59]

Hi,

If your field DemandMonth is numeric, I suggest you to do that :

Data:

LOAD ID,

     ...,

     MakeDate(Year(Today()), DemandMonth) as DemandDate,

     Demand

FROM Data.qvd;

Calendar:

LOAD Distinct DemandDate,

     MonthName(DemandDate) as DemandYearMonth,

     Month(DemandDate) as DemandMonth

RESIDENT Data;

After that, you have to use something like this in design part :

Sum({<DemandMonth={"$(=MonthName(Today(),1))"}>} Demand)

Hope that helps you

Martin Favier

[martin59]

Hi,

If your field DemandMonth is numeric, I suggest you to do that :

Data:

LOAD ID,

     ...,

     MakeDate(Year(Today()), DemandMonth) as DemandDate,

     Demand

FROM Data.qvd;

Calendar:

LOAD Distinct DemandDate,

     MonthName(DemandDate) as DemandYearMonth,

     Month(DemandDate) as DemandMonth

RESIDENT Data;

After that, you have to use something like this in design part :

Sum({<DemandMonth={"$(=MonthName(Today(),1))"}>} Demand)

Hope that helps you

Martin Favier

[jagan]

Hi,

Try this expression, suppose if you select Feb-2013, then the below expression will give you the demand for Mar-2013

=Sum({<DateField={'>=$(=AddMonths(MonthStart(Max(DateField)), 1)<=$(=AddMonths(MonthEnd(Max(DateField)), 1))'}>} Demand)

Hope this helps you.

Regards,

Jagan.

[#6]

Thanks - just to clarify - really I am looking to use the system month as the comparison tool - vs entering a date.  Also - I did link the demanddate field on the table to a master calendar to create fields for Demandweek, demandyear, demandmonth and demandday.  So really this should be a simple comparison of the current(system) month +1 month to the demandmonth field - and then a sum of the demand. 

[#7]

I tried this but it in not returning the values for March - current month +1

Sum({<[Demand Month] = {$(AddMonths(Month,1))

[erichshiino]

Hi,

Sometimes the problem is the date format. I always use fields in numeric format to avoid this problem.

This approach is suggested in this document:

http://community.qlik.com/docs/DOC-3102 (check tip 5)

So, I suggest you create a field like this in script:

Load ... , num( monthstart( [Demand Month] ) ) as nDemandMonth

Set analysis will be:

sum( {<nDemandMonth = {$(=num( monthstart( today())))}>} Value)

Hope it helps,

Erich

[erichshiino]

This recent blog post is also very interesting:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

Regards,

Erich

[#10]

Eric – This is what I have in my master calendar mapping:

QuartersMap:

MAPPING LOAD

rowno() as DemandDate,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(DemandDate) as minDate,

max(DemandDate) as maxDate

Resident DemandDate;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 } Demand)