set analysis: Previous Week

[#1]

Hi,

I did a formula with a set analysis: It's working well (good news)!!

=Sum ({<[Fiscal Year-Week]=[Current Fiscal Year-Week]>} Metric)

Now, I need to do the same but for the previous week.

Current Fiscal Year-Week contain 1 value with the current week not related to other data.

Have you got some ideas?

Thanks,

Eva

[swuehl]

What is the format of your Fiscal Year-Week field? If you use a sequential number over all years, you can probably just subtract 1 from your Current Fiscal Year-Week.

[#3]

My format is YYYY/WW.

[swuehl]

Try something like (untested dollar sign expansion, but the idea should be clear?):

=Sum ({<[Fiscal Year-Week]= {'$(=year(today()-7)&'/'&week(today()-7))'}>} Metric)

[#5]

Hi,

Thanks for your answer, but I cannot use week (today()) because my fiscal week number is not align with the civil week number

[swuehl · Accepted solution]

Try incoporating your fiscal week computation logic into above dollar sign Expansion.

Or introduce an additional calendar week field.

Or use a selection in field date with an advanced search on dates between week start of today -7 date and week end of today -7.

[#7]

If you can change your data model, I would recommend swuehl method of having sequential numbers for weeks. If you make the number sequence in such a way that the current week is always 0, previous week is -1 (or 1 if you don't like negative numbers) then the calculation is easier. That way, your set analysis is simple, e.g., current week is Sum ({<[Fiscal Year-Week Num]={0}>} Metric) and previous week is Sum ({<[Fiscal Year-Week Num]={-1}>} Metric)

[natalia_sterniak]

Hi, I have simmilar problem due to values from the previous week in reference to the next one (not current, as I have future data) 

I just need to display value from the previous week in each column,  details in the picture

The week format is 'YY-WW', but i also added autonumbering.

As far I tried to do that with set anylysis like 

Sum({ <master_calendar_week= { '$(vPreviousWeek)' }>} [Wartość])  where vPreviousWeek =Right(year(imputationDate),2)&'-'&(week(imputationDate)-1)  

and 

=Sum({<[master_calendar_week_Flag]={"$(=([master_calendar_week_Flag]-1))"}>} [Wartość])  where master_calendar_week is autonumbered week 

but both of them don't work

The only formula which works it is:

=Sum({<[master_calendar_week_Flag]={"$(=max([master_calendar_week_Flag]-1))"}>} [Wartość])  

and it displays value for max week-1 correctly, but only for this one and affect other measures..... 

Can anybody help me with that  one, please?

Regards