Question

[jadams123]

Hi All:

I want to be able to do this statement:

1) if max week -1 is between weeks 40 to 44 then P10.

This is my formula for max(Week -1):

=Sum( {$<YEAR = {$(#=Max(YEAR))}, [Week No]= {$(#=Max([Week No])-1)}, CUSTOMER_TYPE -= {ITC}, CREDIT_CODE = {YES}>} AMOUNT )

This is my formula for max(period):

=Sum( {$<GLYEAR = {$(#=Max(GLYEAR))}, GLPERIOD= {$(#=Max(GLPERIOD))}, CUSTOMER_TYPE -= {ITC}, CREDIT_CODE = {YES}>} AMOUNT )

Thanks,

J

[jadams123]

Basically what I want to do...if some one has an easier method is:

what ever (week-1) is grab that period and sum sales for the period.

[jadams123]

oh by the way I want to keep in the formula somehow...

GLYEAR = {$(#=Max(GLYEAR))}

[vinieme12 · Accepted solution]

Try below

=Sum( {$<YEAR = {$(#=Max(YEAR))}, GLPERIOD = P({<[Week No]= {$(#=Max([Week No])-1)}>}GLPERIOD), CUSTOMER_TYPE -= {ITC}, CREDIT_CODE = {YES}>} AMOUNT )

[jadams123]

This formula worked perfectly.

Now I have a problem with YTD

I want to do...

Sum YTD till week-1

[vinieme12]

Try below

=Sum( {$<YEAR = {$(#=Max(YEAR))}, [Week No]= {"<=$(#=Max([Week No])-1)"}, CUSTOMER_TYPE -= {ITC}, CREDIT_CODE = {YES}>} AMOUNT )

[jadams123]

=Sum( {$<[Week No] = {"<$(#=Max([Week No]))"}, DateType = {'GL Date'}, CUSTOMER_TYPE = {*} - {ITC}, CREDIT_CODE = {YES}>} AMOUNT )

I am trying this formula...its giving me the correct answer.... but not sure if this is the correct way of doing it.

I thought I would be using week -1 some where. 

[vinieme12]

Are you sure this is giving you correct YTD?

You haven't specified Year anywhere so it would give you sum of  week number less than example.  week 1-35 for all possible years

[jadams123]

ya you are right it was giving me all possible years...I have 2017 selected (even though I know the reason for an object is not to have any/limited selections)

If I clear all my selections this formulas doesn't give me correct answer

if today is week 2017 week 44 I wanted sales for 2017 week 43 -->[Week No]= {$(#=Max([Week No])-1)},

=Sum( {$<YEAR = {$(#=Max(YEAR))}, [Week No]= {"<=$(#=Max([Week No])-1)"}, CUSTOMER_TYPE -= {ITC}, CREDIT_CODE = {YES}>} AMOUNT ).

I know max is supposed to do this. but for some reason its not.

[vinieme12]

[Week No]= {$(#=Max([Week No])-1)},    << I think it's because the "#' before the equal sign.....that # is not needed anywhere