how can i compare the values between two weeks ie current week and previous week also i want to post the value of the current week if its greater than the previous week into a different account member

[former_member1235400]

how can i compare the sales value between two weeks ie current week and previous week also i want to post the value of the current week if its greater than the previous week into a different account member any suggestions

[former_member1235400]

why i am confused is week is not a time dimension here , WK01, 02 03...we have another time dimension , the scenario is if the current week sales is more than the record sale , that week sales should be updated as the record sale figure , and record sales is a member in a custom dimension, sales is in account dimension

[former_member186338]

Sorry, then please explain everything in full details, just not to waste time...

[former_member1235400]

ENTITY

ACCOUNT

VERSION

TIME

WEEK

CUSTOMER TYPE

DATASRC

PPC

PCAT

RPTC

STAT

SUBDEPT

SIGNDDATA

xyz

SALES

ACTWK

2016.03

WK15

NOCTYP

LOAD_SS

I_NONE

NOPCAT

LC

RECORD SALES

NOSUBDPT

10000

previous record sales for this Profitcenter was this

xyz

SALES

ACTWK

2016.03

WK17

NOCTYP

LOAD_SS

I_NONE

NOPCAT

LC

NOSTAT

NOSUBDPT

10001

now on wk17 sales was more than record sale

xyz

SALES

ACTWK

2016.03

WK17

NOCTYP

LOAD_SS

I_NONE

NOPCAT

LC

RECORD SALES

NOSUBDPT

10001

then this record should updated as record sale

xyz

SALES

ACTWK

2016.03

WK15

NOCTYP

LOAD_SS

I_NONE

NOPCAT

LC

RECORD SALES

NOSUBDPT

(10000-)

this should negate previous record sales

[former_member186338]

In WEEK dimension you have to create property PREVWEEK and fill it with previous WEEK member!

Then:

*LOOKUP SameModel

*DIM PW:WEEK=WEEK.PREVWEEK

*ENDLOOKUP

*XDIM_MEMBERSET WEEK=%WEEK_SET% //current week

*XDIM_MEMBERSET TIME=2016.03

*XDIM_MEMBERSET ACCOUNT=SALES

*XDIM_MEMBERSET VERSION=ACTWK

*XDIM_MEMBERSET ENTITY=xyz

*WHEN WEEK

*IS * //Current week

*REC(EXPRESSION=(%VALUE%>LOOKUP(PW)) ? %VALUE% : 0, ACCOUNT=TARGETACCOUNT)

*ENDWHEN

[former_member1235400]

but the week can be any of the previous weeks of prior year or current year

[former_member186338]

I assume you have continues week members crossing years

Anyway, I do not understand how you load data for the weeks that cross year or month boundary ...

Please explain in details!

Vadim

[former_member1235400]

week dimension will have week numbers with parent months for eg wk01 will be the children for jan, and TIME will be maintained as a property.

coming to data aspect data will be stored as below in model

TIME       WEEK  ACCOUNT STAT       PRODUCT CAT .... SIGNEDDATA

2015.01    WK01     XXXX      RCRDSALE    HEALTHCARE ....10000

2015.02     WK02     XXXX     NOSTAT     PHARMA     -----  2000

Here the requirement is if the current week for eg wk02 value with NOSTAT is more than any of the previous week value with  RCRDSALE , current wk value should be copied and posted as another transaction with stat member RCRDSALE for the specific entity, if its not greater than record sales , nothing to be done

[former_member186338]

Sorry, but you are doing something strange:

"week dimension will have week numbers with parent months"

What months you are talking about?

How it's related with TIME dimension?

Etc...

[former_member1235400]

*SELECT (%CURRENTWK%,CURRENTWEEK,VERSION,ID = "ACTWK")

*SELECT (%CURRENTMTH%,CURRENTMONTH,VERSION,ID = "ACTWK")

*LOOKUP FORECASTING

*DIM CWSALE:TIME = %CURRENTMTH%

*DIM CWSALE:WEEK = %CURRENTWK%

*DIM CWSALE:ACCOUNT = 41110000

*DIM CWSALE:CUSTOMERTYPE = NOCUSTOMERTYPE

*DIM CWSALE:PROFITCENTER = NOPROFITCTR

*DIM CWSALE:DATASRC = INPUT

*DIM CWSALE:PARTNERPC = I_NONE

*DIM CWSALE:PRODUCTCAT = NOPRODUCTCAT

*DIM CWSALE:RPTCURRENCY = LC

*DIM CWSALE:SUBDEPT = NOSUBDEPT

*DIM CWSALE:VERSION = ACTWK

*DIM CWSALE:MEASURES = PERIODIC

*DIM CWSALE:STAT = NOSTAT

*ENDLOOKUP

*XDIM_MEMBERSET CUSTOMERTYPE = NOCUSTOMERTYPE

*XDIM_MEMBERSET DATASRC = INPUT

*XDIM_MEMBERSET PARTNERPC = I_NONE

*XDIM_MEMBERSET PRODUCTCAT = NOPRODUCTCAT

*XDIM_MEMBERSET PROFITCENTER = NOPROFITCTR

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET SUBDEPT = NOSUBDEPT

*XDIM_MEMBERSET VERSION = ACTWK

*XDIM_MEMBERSET TIME = BAS(2015.TOTAL)

*XDIM_MEMBERSET STAT = RCRD_SALES

*XDIM_MEMBERSET WEEK = NOWEEK

*XDIM_MEMBERSET MEASURES = PERIODIC

*XDIM_MEMBERSET ACCOUNT = 41110000

*WHEN WEEK

  *IS NOWEEK

*REC(EXPRESSION = (%VALUE% < LOOKUP(CWSALE))?LOOKUP(CWSALE):0,WEEK = NOWEEK, STAT = RCRD_SALES , TIME = %TIME_SET%)

*ENDWHEN

*COMMIT

approach is to store the record sales in noweek member but what ever the value is its copieing to record sale

[former_member1235400]

every week member will have a time property which will be a valid member in time dimension

[former_member186338]

Please answer my prev questions!

Weeks are not related to months and years! What is the logic???

[former_member1235400]

months are in time dimension , every week data load with current week member and the time context will be corresponding month , is it clear

for eg : 2016.01 will have wk01 , wk02 , wk03 and wk04 transactions at the end of the month

[former_member186338]

No, weeks start in one month and end in another! Same for year!

[former_member1235400]

their will be 48 weeks in an year for 12 period so wk 05 will comes on 2016.02 and so on

[former_member186338]

Can you explain this strange calendar? Months - are not real months? Weeks are not real weeks? Etc?

[former_member1235400]

The model has Time as time dimension and will hold the time values 2015.01 , 02 , 03 ...

week as a costume dimension which holds week numbers whihc will be wk01,02,03..

now when every week data is loaded will load the data with current month and current week combination

[former_member186338]

I am talking about calendar!

Calendar month starts at the middle of the calendar week and ends in the middle of calendar week:

October 2015 - starts in the middle of calendar week - how you will handle it? Show some sample with dates?

[former_member1235400]

every Monday will be considered as a new week starting, so if the month starts on the middle of the week till Mondays data will be loaded as current week and next week week will be for the new month

[former_member1235400]

we have a property which indicates the week is a last week of that month or not , which will help in dealing with the scenario you mentioned

[former_member186338]

Ok, it means you will have normal calendar weeks from Monday to Sunday and artificial months starting with the first full week! And you will also have artificial years combining 12 artificial months! Number of weeks in months will be different. Year end will on different days!

Is it correct?

[former_member1235400]

sure

[former_member186338]

Assuming 365 days per year you may have 52 or 53 7 day weeks per year (53 if 1 of Jan is Monday). Why are you talking about 48 weeks?

[former_member1235400]

just said for an example , there are 53 week in this period from jul to jun

[former_member186338]

Incorrect example results in misunderstanding

Now the picture is more or less clear and I recommend to remove weeks dimension and implement weeks as a base members of time dimension.

Vadim

[former_member1235400]

cant do that only for this requirement, have to do some workaround to achieve this

[former_member186338]

It's not for this requirement only. It's a correct TIME dimension setup... With this additional WEEK dimension you will have endless issues. But it's your choice!

[former_member186338]

Something like this:

//%TIME_SET% - contains current week, weeks are base members

*XDIM_MEMBERSET TIME=%TIME_SET%

*XDIM_MEMBERSET ACCOUNT=SOMEACCOUNT1

*WHEN TIME

*IS * //Current week

*REC(EXPRESSION=(%VALUE%>[TIME].[TMVL(-1,%TIME_SET%)]) ? %VALUE% : 0, ACCOUNT=SOMEACCOUNT2)

*ENDWHEN

Vadim