Set analysis expression to calculate day over day, week over week using a date variable

[#1]

Hello,

I have 2 date variables vStartDate and vEndDate. I am trying to figure out  a set analysis expression to calculate day over day, week over week, month over month, year over year calculation.

i am using following expression to get data for vEndDate

sum({<Prod_type = {'aa'}, Date = {">=$(=timestamp(date#($(=chr(39) & vEndDate & chr(39)),'D-MMM-YYYY'),'M/D/YYYY hh:mm:ss TT'))<=$(=timestamp(date#($(=chr(39) & vEndDate & chr(39)),'D-MMM-YYYY'),'M/D/YYYY hh:mm:ss TT'))"}>}USD_VALUE)

With a timestamp conversion around dates, I am having difficulty coming up with an expression to subtract a day or add weekday logic.

Thank you so much.

[sunny_talwar]

[sunny_talwar]

I am sure Stefan will help you get to what you are looking for, but for you to get to the correct answer you have to decide what exactly are you trying to do. Can you share few lines of sample with an expected output? The more details and clarity you will offer us, the faster we will be able to help you out.

Best,

Sunny

[#4]

Apologies if you found my questions confusing, but from the time I posted the question to now, I was able to figure out the calculations for PrevDay  and 7PrevDay. My current problem is to come up with a formula to calculate previous month's date and previous year's date.

Formulas that work:

vPrevDay: =date(date#(vEndDate,'DD-MMM-YYYY') - if(date(date#(vEndDate,'DD-MMM-YYYY'),'WWW')='Mon',3,1) ,'DD-MMM-YYYY')

v7daysPrev: =date(date#(vEndDate,'DD-MMM-YYYY')-7,'DD-MMM-YYYY')

Formulas I came up with but not work:

vPrevMonth: =Year(addmonths((vEndDate),-1))

vPrevYear: =date(addyears((vEndDate),-1),'DD MMM YYYY')

[sunny_talwar]

It is not confusing, but you have changed the requirement. What we are asking you is to create another thread for a new requirement and close this one down, otherwise we will be working on this same thread forever and will be very confusing for future lookers to understand what actually was the question of this thread.

Anyways, I am checking if there is a way to handle what you have just mentioned

[#6]

ah, got it makes sense. Will open a new thread.

thanks both of you for your help.

[sunny_talwar]

Thank you

[sunny_talwar · Accepted solution]

Can you try these expressions:

1 month back:

=Date(AddMonths(vEndDate, -1) - If(Date(AddMonths(vEndDate, -1),'WWW') = 'Sun', 2, 0)

  - If(Date(AddMonths(vEndDate, -1),'WWW') = 'Sat', 1, 0), 'DD-MMM-YYYY')

1 year back:

=Date(AddYears(vEndDate, -1) - If(Date(AddYears(vEndDate, -1),'WWW') = 'Sun', 2, 0)

  - If(Date(AddYears(vEndDate, -1),'WWW') = 'Sat', 1, 0), 'DD-MMM-YYYY')

[#9]

Thank you very much that works, I tweaked it to :

=Date(AddMonths(date(date#(vEndDate,'DD-MMM-YYYY')), -1) - If(Date(AddMonths(date(date#(vEndDate,'DD-MMM-YYYY')), -1),'WWW') = 'Sun', 2, 0)
- If(Date(AddMonths(date(date#(vEndDate,'DD-MMM-YYYY')), -1),'WWW') = 'Sat', 1, 0), 'DD-MMM-YYYY')