Previous Date for special field

[Anonymous]

I have an Excel that I am pulling into a table.

When I create a DATE list box, the CALENDAR MONTH will show the related CALENDAR MONTH and the PERIOD WD will render the equivalent PERIOD WD,

Can I have an expression that will show in a Straight Table the Previous Period WD - based on the Current Selection? 

I tried several vPrev type variables and I cannot get it to show the Previous Period WD.  Below is how the Excel data looks.

Thanks

Dan

[danieloberbilli]

Hi Dan,

Not sure if this example works for you...but maybe gives you an idea about a possible approach:

Please find .qvw attached

LOAD *,
WeekDay(DateField) as WD,
num(WeekDay(DateField)) as numWD,
num(WeekDay(DateField))-1 as prevNumWD,
date(DateField-1)    as preDate
;
LOAD * INLINE [
    DateField
    01/01/2014
    02/01/2014
    03/01/2014
    04/01/2014
];

[r_wroblewski]

Hi Dan,

I would create an additional field (same table Period WD) where each Period WD is represented by a number.

Then you can use this number in Set Analysis to achieve what you want.

e.g. Minimum Period WD = 1 & nPeriod WD = n

If you can provide a sample file with dummy data it would be easier to give you a more detailed solution.

Regards,

Ronny

PS: Result should be same as Daniels approach.

[Anonymous]

Ronnie,

Here is a sample of the data.

Thanks Dan

[Anonymous]

Daniel,

Thanks- will toy around with this.

[danieloberbilli · Accepted solution]

Please find attached based on your sample data

[Anonymous]

Thanks Daniel and Ronny I was able to get the prevDate to work!

[r_wroblewski]

Hi Dan

Good to hear that it`s working.

Regards,

Ronny