Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Looking for help from the community:
I have a simple table that looks something like this:
Date Type Value
09/09/2017 S 100
10/09/2017 S 100
I am trying to get a set analysis work that sums Values but only where the week number of the Date is equal to the current week number (in future I may change this to be some other variable).
What I've tried:
--------------------
Created a variable to get current week number:
vCurWeek = Week([Date], 0)
Used the following measure in my table:
Sum({$< Week([Date],0) = {$(vCurWeek)} >} Value)
But get no results.
I've tested the variable as well as using Week([Date], 0) in a text box/table and both are ok. I just can't get it to work as part of the set analysis, despite it looking pretty much the same as the correct answer in this post: Help to SET Analysis - get past 4 weeks
Thank you!
Try like this,
Fact:
load *,Week(Date) as Week;
load * Inline [
Date, Type, Value
09/09/2017, S, 100
10/09/2017, S, 100
];
create variable as
vCurWeek
=Week(max(Date))
Set analysis expression as
=Sum({$< Week = {$(vCurWeek)} >} Value)
P.S: best way u can use master calendar script
HI,
You will not be able to use like you have it. The highlighted portion in red has to be replaced by a weeknumber field that you may have calculated in your script.
Sum({$< Week([Date],0) = {$(vCurWeek)} >} Value)
In the post you have included it is a field comparison to a variable
Count({<weeknumber = {$(vCurrentWeek)}>} solditems).
Providing a larger sample data and your script may help us to help you.
Best Regards,
Vijay
mstDate:
LOAD * INLINE
[
minDate, Type, Value
09/09/2017, S, 100
10/09/2017, S, 100
];
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(date(minDate,'DD-MM-YYYY'),) as minDate,
max(Today()) as maxDate
Resident mstDate;
drop table mstDate;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
Let vCurWeek = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate,
Date($(CurWeek) + IterNo()-1) as WeekDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS Date,
num( TempDate) AS Datenum,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate)As Month,
Week(WeekDate) as CurWeek,
'Q' & Ceil (Month(TempDate)/3) as Quarter1 ,
num(Month(TempDate)) As MonthNum,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
num(date(monthstart(TempDate), 'MMM-YYYY')) as MonthYearNum,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate))&'-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
And
Set analysis expression as
=Sum({$< Week = {$(vCurWeek)} >} Value)