Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to calculate previous week id's total:
Note that I have these following fields in my Data model:
Week Id = 2015-W02 or 2014-W09 like this and
Process Date= 09/31/2017 like this
So I am using the below set analysis, but it gives me nothing:
'Prior Week Total= ' & sum({<[Week ID]={"$((=year([Process Date])) & '-W' & (=num(week([Process Date])-1,'00')))"}>}[Sales Posting Amount USD])
So when I choose week id from drop down it will show previous week's value.
Please help me about this.
Thanks,
Sandip
Can you share a sample to look into?
Hi,
I hope your trying to do in Text object, if so just try like below,
='Previous Week Sales: '& Num(Sum({<Date = {">=$(=Week(Max([Process Date]), -1)) <= $(=Date(Max([Process Date]), -7))"}>} TotalNetPriceSGD),'#,##0.00')
Hi,
using backend method,
1) Try to add below WeekSerial column in your calendar script
AutoNumber(weekyear(Date) &'|' & week(Date) as WeekSerial
2) then use the WeekSearil in front end using set analysis method
sum({<Cal_WeekYear=, Cal_Year=, WeekSerial={$(=max(WeekSerial)-1)}>}
'Prior Week Total= ' & sum({<WeekYear=,Year=, [Week ID]={"$((=year([Process Date])) & '-W' & (=num(week([Process Date])-1,'00')))"}>}[Sales Posting Amount USD])
Hope this helps you
Hi,
What is 'Date=' in your expression(in Bold face font)?
Is it a function or column name here:
='Previous Week Sales: '& Num(Sum({<Date = {">=$(=Week(Max([Process Date]), -1)) <= $(=Date(Max([Process Date]), -7))"}>} TotalNetPriceSGD),'#,##0.00')
or it should be as:
Num(Sum({<[Process Date]= {">=$(=Week(Max([Process Date]), -1)) <= $(=Date(Max([Process Date]), -7))"}>} TotalNetPriceSGD),'#,##0.00')
please correct me if I am wrong.
Also I need to use it in a straight table. Will it work there?
Thanks,
Sandip
Hi,
Why you are using max(WeekSerial) - means why you are using max() function here?
sum({<Cal_WeekYear=, Cal_Year=, WeekSerial={$(=max(WeekSerial)-1)}>}
Thanks,
Sandip