Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Not applicable

Set Expression with Dates

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

13 Replies
martin59
Valued Contributor II

Re: Set Expression with Dates

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
Valued Contributor II

Re: Set Expression with Dates

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
Valued Contributor II

Re: Set Expression with Dates

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

MVP & Luminary
MVP & Luminary

Re: Set Expression with Dates

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.

Not applicable

Re: Set Expression with Dates

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. 

Highlighted
Not applicable

Re: Set Expression with Dates

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

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

Partner
Partner

Re: Set Expression with Dates

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

Partner
Partner

Re: Set Expression with Dates

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

Not applicable

Re: Set Expression with Dates

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)