Date

[mario-sarkis]

Hey all,

my question is i have a field of Date and amount for example:

PerioDate : is to field of Date as number

i need to calculate in a BAR CHART

1- Sum({<periodDate=MaxDate>}amount)  (Current date)

2- Sum(({<periodDate=YearAgo>}amount)  (YearAgo)

3- Sum(({<periodDate=LastMonth>}amount)  (Last Month)

Please help

[#2]

Hi,

Try like below,

Exp: =Sum({<periodDate={"$(=Max(periodDate))"}>}amount)  (Current date)

Calculate field at script level like below,

year(periodDate) as periodDateYear

Then, Take below Dimension & expression under bar chart

Dim:periodDateYear

Exp: =Sum({<periodDateYear={"$(=year(periodDate)-1)"}>}amount)  (YearAgo)

Calculate field at script level like below,

month(periodDate) as periodDateMonth

Then, Take below Dimension & expression under bar chart

Dim:periodDateMonth

Exp: =Sum({<periodDateMonth={"$(=month(max(periodDate))-1)"}>}amount) (Last Month)

[SatyaPaleti]

Mario,

Try this

Sum({<periodDate={"$(=Max(periodDate))"}>}amount)  as Current date

Sum({<periodDateYear={"$(=Year(periodDate)-1)"}>}amount)  as YearAgo

Sum({<periodDateMonth={"$(=Month(max(periodDate))-1)"}>}amount) as Last Month

Thank you,

Satya paleti

[mario-sarkis]

Hi Satya

if i am in 2/27/2015 and want the summ in 2/27/2014 is the expression of year ago that you gave me will be applicable ?

Thank you,

[SatyaPaleti]

Mario,

Try This

Sum({<periodDateYear={"$(=Max(Year(periodDate))-1)"}>}amount)  as YearAgo

Thank you,

SatyaPaleti

[PrashantSangle · Accepted solution]

Hi,

try below

Sum({<periodDate={"$(=Max(periodDate))"}>}amount)  as Current date

Sum({<periodDate={"$(=AddYears(max(periodDate),-1))"}>}amount)  as YearAgo

Sum({<periodDate={"$(=AddMonths(max(periodDate),-1)"}>}amount) as Last Month

Note: check Dateformat

Regards

[mario-sarkis]

hi Max PeriodDate format is num is this still applicable thanks:)

[PrashantSangle]

Hi,

I think above expression will work for you, If not use Num()

Modify above expression like

Sum({<periodDate={"$(=Max(periodDate))"}>}amount)  as Current date

Sum({<periodDate={"$(=num(AddYears(max(periodDate),-1)))"}>}amount)  as YearAgo

Sum({<periodDate={"$(=num(AddMonths(max(periodDate),-1)))"}>}amount) as Last Month

Regards

[mario-sarkis]

hey Max thank you for your help ,

but i am facing a small problem wich is :

lets say i have the date 2/27/2015 but in on year back i have it as 2/26/2014 i dont have 2/27/2014

so using your expression it will show a zero amount in 2/27/2014 (amount is avaible on day in a month elsa null)

is it possible to create a field that contain YEARMONTH ? if yes how and how can i use it in this expression

Thank you hope that you can help.

[PrashantSangle]

Hi,

If you want to create YearMonth field then you can use MonthName() it will gives MonthYear

You can also use Date(Fieldname,'YYYYMM') as YearMonth

Regards