topic Re: Set Expression with Dates in QlikView

Set Expression with Dates

Mon, 18 Feb 2013 15:17:47 GMT

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

Re: Set Expression with Dates

martin59 — Mon, 18 Feb 2013 15:29:49 GMT

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

Re: Set Expression with Dates

martin59 — Mon, 18 Feb 2013 15:29:56 GMT

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

Re: Set Expression with Dates

martin59 — Mon, 18 Feb 2013 15:30:02 GMT

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

Re: Set Expression with Dates

jagan — Mon, 18 Feb 2013 15:33:43 GMT

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.

Re: Set Expression with Dates

Mon, 18 Feb 2013 15:38:40 GMT

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.

Re: Set Expression with Dates

Mon, 18 Feb 2013 20:32:43 GMT

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

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

Re: Set Expression with Dates

erichshiino — Tue, 19 Feb 2013 14:29:12 GMT

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

Re: Set Expression with Dates

erichshiino — Tue, 19 Feb 2013 17:50:16 GMT

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

Re: Set Expression with Dates

Tue, 19 Feb 2013 18:18:05 GMT

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)

Re: Set Expression with Dates

erichshiino — Tue, 19 Feb 2013 18:30:54 GMT

What's the format of [Demand Month] or Month in your expression?

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

Sometimes, addmonths or other functions will reset the format to a date or timestamp.

You can try this:

Sum({<[Demand Month] = {$(=num(AddMonths(Month,1)))} > } Value)

If you try this fuction in a straight table without any label on the expression, you will be able to see what happend inside the {}. There, it should be like this:

Sum({<[Demand Month] = {3} >} Value)

Regards,

Erich

Re: Set Expression with Dates

Tue, 19 Feb 2013 19:20:18 GMT

Here is what I got in the label: sum({<[DemandMonth] = {41306} > } Demand)

Re: Set Expression with Dates

erichshiino — Wed, 20 Feb 2013 03:15:16 GMT

What do you have when you put the field DemandMonth in a listbox? It should be in the same format ( 41000, for example).

If it is not, you can set it in document settings -> number ( be careful to not include thousand separators or decimals).

Check if the value 41306 exists.

Regards,

Erich

Re: Set Expression with Dates

Wed, 20 Feb 2013 13:22:43 GMT

Erich,

I got some help from a QV resource and what we did was set up an environment variable in the settings for the current month, next month, etc. Then we referenced the variable in the set expression. This worked and solved the issue.

Thanks, David