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.

[kaushiknsolanki]

Hi,

Create a week from Date using.

Week(Date) as Week

And also have Year field created from Date

Year(Date)

Use below Expressions.

1. Sum(Sales)   ---- For elected Week.

2. Sum({<Week = {"$(=week(max(Date)-7))"}>}Sales)

Regards,

Kaushik Solanki

[swuehl]

You can create a sequential field counting the weeks in your master calendar, maybe using autonumber(), then use this field in your set analysis

MasterCalendar:  //DateField input records come in sorted asc

LOAD

          DateField,

          Autonumber(Weekstart(DateField), 'Week') as WeekNoSeq,

          ...

=Sum({<WeekNoSeq = {'$(=Max(WeekNoSeq))'}>}Sales)

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

[pavanqlik]

Thanks for your response

I tried it,but WeekNoSql is not coming in order

[swuehl]

This might depend on the way you create your DateField.

You should be able to create a sequential order independent from load order of DatField using

Div(WeekStart( DateField), 7) as WeekNoSeq,

[pavanqlik]

Hi Swuehl,

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

[Anonymous]

might be helpful for you?

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

[tresesco]

Could you post a sample qvw?

[HirisH_V7]

Hi,

May be like this,

I have taken a sample data,

Create a Variable,

=max({<Flag={'week'} , Year={'$(=Max(Year))'} >}Week)

In straight Table,

=IF( vWeek=1,(Sum({<Flag={'week'} , Year={'$(=Max(Year))'},Week={$(=vWeek)}>}Sales)
-
Sum({<Flag={'week'} , Year={'$(=Max(Year)-1)'},Week={53}>}Sales)),


(Sum({<Flag={'week'} , Year={'$(=Max(Year))'},Week={$(=vWeek)}>}Sales)
-
Sum({<Flag={'week'} , Year={'$(=Max(Year))'},Week={$(=vWeek-1)}>}Sales))

)

There by ,When you select week 1 , condition is set to pick previous year max(Week) and if other means 2 weeks current and previous week.

Hope this helps,

PFA,

Hirish

[jagan]

Hi Pavan,

Check this links for similar type of expressions

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

Regarsd,

Jagan.Current week Vs Previous week