Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I am using the below to attempt to sum up values in the field quantity advised for the last week ie Monday 11th to Sunday 17th (week 24). The problem is if I compare the value from the expression below and then sum the fields in excel for the same week (24) they are not the same. Is the expression below modified when it is used ie Running it yesterday would give different result to today .
Sum({<ColYear={'2018'},ColWeek = {"$(=Week(Today())-1)"}>}[Quantity Advised])
Thanks
Del
This could be because of any number of reasons... would you be able to share the Excel file and the app you are using for the comparison?
share your app??
Regards,
Thanks.
Unfortunately I am using the desktop freed version. I want o be able to run the KPI visualization anytime this week and get the quantity advised for the preceding full week. ie run the app today and get data for 11th -17th, run it tomorrow and still get data for the 11th to 17th.I did use Josh Goods approach to generating the master calendar to produce my dates below:
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(Date_Collected) as minDate,
max(Date_Collected) as maxDate
Resident [All Orders];
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 <= $(varMaxDate);
CollectCalendar:
Load
TempDate AS Date_Collected,
week(TempDate) As ColWeek,
Year(TempDate) As ColYear,
Month(TempDate) As ColMonth,
Day(TempDate) As ColDay,
YeartoDate(TempDate)*-1 as ColCurYTDFlag,
YeartoDate(TempDate,-1)*-1 as ColLastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as ColRC12,
date(monthstart(TempDate), 'MMM-YYYY') as ColMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as ColQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as ColWeekYear,
WeekDay(TempDate) as ColWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;