Current week Vs Previous week

[pavanqlik]

Hi Guys,

I had a scenario, where i had a closed date field.

I am taking week from close date. when a user click on w32 in list box, i need to see columns in staright table with w32 current week and w31 previous week of same year.

If i select w1 of 2014 , then i need to see two columns with W1 2014 as one and W52 2013 as second.

I am doing sum of sales for Current and Previous week across Account Name.

Please help me in getting the expressions.

[swuehl]

I have closed dates as only one date that to Wednesday every week.

This should work also with only a date record per week. Didn't it work using Div() function and Weekstart()?

Just take care to clear selections in calendar field when querying the previous week, to avoid incompatible set assignments. So when the user may select in CloseDate, you need to clear field CloseDate:

=Sum({<WeekNoSeq = {'$(=Max(WeekNoSeq)-1)'},CloseDate= >}Sales)

and all other calendar fields that the user may select in.

[PradeepReddy]

i think we have to use CEIL function as well, like bellow...

Ceil(WeekStart(Date_Field)/7) as WeekSeq

EX:

Ceil(Weekstart(Date#('1900-1-1','YYYY-MM-DD'))/7)  returns 1

DIV(Weekstart(Date#('1900-1-1','YYYY-MM-DD')),7)  returns 0

[swuehl]

I believe there is just a constant offset of 1 for all weeks between these two functions. Doesn't really matter IMO.

[pavanqlik]

Hi swuehl,

I am able to generate seqnumber correctly, but when i am writing expression =Sum({<WeekNoSeq = {'$(=Max(WeekNoSeq)-1)'},CloseDate= >}Sales) for prev week its not working

when using single quotes in expression its not working , but when i removed its working fine but am having a issue here

If i select Year,Quarter and Week filters in my dashboard i am able to see current week information in st table but prev week is showing as 0

But when i unselect week selection and when i make selection in seqnumber list box iam able to see both current week and prev week in st table.

Could you please help me on this.....

[swuehl]

As I tried to explain above, you need to take care the the field modifications made in the set expression are compatible with user selections. If you make selections in field Week, this selection will be incompatible with a field modifier trying to select the previous WeekNoSeq. Hence you need to clear the user selections in calendar fields in the set expression:

=Sum({<WeekNoSeq = {'$(=Max(WeekNoSeq)-1)'},CloseDate=, Week=, Year=, Quarter= >}Sales)

[add more calendar fields as required.]

[natalia_sterniak]

Hi, I have simmilar problem due to values from the previous week in reference to the next (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 work 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?