need help for a set analysis

[ananyaghosh]

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

[vishsaggi]

Can you share a sample to look into?

[devarasu07]

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')

[devarasu07]

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

[ananyaghosh]

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

[ananyaghosh]

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