Subtract 1 month from Date Variable in set analysis

[#1]

Hello,

I have a a straight table that i need to calculate a sum of values based on (month selected - 1 Month).So for instance i select May, I want to be able to get te value of April (May-1) and sum that. How can i do this. Below is an example of my code:

=sum({<[PayType]={'PD'},[ReportMonth]={'$(=AddMonths(vReportMonth,-1))'}>}d.TotalDCN)

Please help

[#2]

Try:

=sum({<[PayType]={'PD'},[ReportMonth]={"$(=Month(AddMonths(vReportMonth,-1)))"}>} d.TotalDCN)

Basically the expression in set anlaysis and repoortmonth should be in the same format.

Kiran.

[#3]

Kiran,

I tried it but it still doesnt work.

[#4]

hi,

Try This

=sum({<[PayType]={'PD'},[ReportMonth]={$(=Month(num(vReportMonth)-1))}>}d.TotalDCN)

[#5]

=sum({<[PayType]={'PD'},[ReportMonth]={"$(=Month(AddMonths(Max([ReportMonth]),-1)))"}>} d.TotalDCN)

Just check the format of ReportMonth with Month(AddMonths(Max([ReportMonth]),-1)).

Kiran.

[#6]

Hi,

Have you solved this,

I think that should work,

=sum({<[PayType]={'PD'},[ReportMonth]={$(=(num(Month(Date#(vReportMonth,'MMM')))-1))}>}d.TotalDCN)

[#7]

Thanks Arun,

I could not get this to work correctly, I ended up doing it in the script using the peek/previous function  and I got it to work

[#8 · Accepted solution]

I used this:

Load

      pd.CurrentDCN,

      pd.CurrentNTK,

      if (pd.ReportMonth = Peek('pd.ReportMonth',-1), pd.CurrentDCN, Previous(pd.CurrentDCN)) as pd.PreDCN,

      if (pd.ReportMonth = Peek('pd.ReportMonth',-1), pd.CurrentNTK, Previous(pd.CurrentNTK)) as pd.PreNTK

[Anonymous]

Hi everyone,

I have been having a problem with a formula in set analysis:

sum(aggr(

if([Payment Type ID] = 1

  ,sum({< [Calendar Year Month ID] =  {'$(=max([Calendar Year Month ID]))'}, [Payment Type ID] = {1}, [Calendar Date]=  {'<$(=date(today(0)-4))'}>}  _f_OK_POSITIVE_TRX)

  ,sum({< [Calendar Year Month ID] =  {'$(=max([Calendar Year Month ID])-1)'},[Payment Type ID] = {2},[Calendar Date]=  {'<$(=date(AddMonths(today(0)-4,-1))'}>}  _f_OK_POSITIVE_TRX)

  )

,[Merchant ID], [Payment Type ID],[Calendar Date]))

For some reason this part:

=date(AddMonths(today()-4,-1))

works in a text box, but when I use it the set analysis it doesn't. Is there anything I'm missing?