Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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.
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.
I tried this but it in not returning the values for March - current month +1
Sum({<[Demand Month] = {$(AddMonths(Month,1))
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
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
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)