Table formula for previous month data

[#1]

Hi,

Within this overview I have selected one month of data. In the column Sum([#quantity]) the volumes per sublevel are depicted.

Now, in the next column I want the volume of the previous month. Currently this is set at:

sum({<Month=, Date={">=$(=monthstart(max(Date),-1))<=$(=monthend(max(Date),-1))"}>} [#Quantity])

However, it does not work as you can see. Any idea how to resolve this?

The field is Month, and the data is Sum([#quantity])

[lironbaram]

try this expression

sum({<Month=, Date={">=$(=monthstart(addmonths(max(Date),-1))) <=$(=monthend(addmonths(max(Date),-1)))"}>} [#Quantity])

[sunny_talwar]

What is your date field format? May be try like this:

Sum({<Month =, Date = {">=$(=Date(MonthStart(Max(Date), -1), 'DateFieldFormatHere'))<=$(=Date(MonthEnd(Max(Date), -1), 'DateFieldFormatHere'))"}>} [#Quantity])

[#4]

Liron, thanks for helping out...tried both with DateFieldFormatHere= 'Date'

Sum({<Month =, Date = {">=$(=Date(MonthStart(Max(Date), -1), 'Date'))<=$(=Date(MonthEnd(Max(Date), -1), 'Date'))"}>} [#Quantity])

your first option did not work either...

sum({<Month=, Date={">=$(=monthstart(addmonths(max(Date),-1))) <=$(=monthend(addmonths(max(Date),-1)))"}>} [#Quantity])

[sunny_talwar]

Date is not a format. Format would be something like this DD/MM/YYYY or DD-MM-YYYY or MM/DD/YYYY and so on.....

Assuming it is DD/MM/YYYY, you can try this

Sum({<Month =, Date = {">=$(=Date(MonthStart(Max(Date), -1), 'DD/MM/YYYY'))<=$(=Date(MonthEnd(Max(Date), -1), 'DD/MM/YYYY'))"}>} [#Quantity])

[#6]

Sunny

seems that the malfunction is in the Month field....in this field Month is January, February etc....in another app I used I had the field #month , month being 1,2, 3 etc..

how would that change things??

[sunny_talwar]

The expression is not even using Month field to cause any issue. If you still wish to change Month you can try this in the script:

Num(Month(Date#(Month, 'MMMM'))) as Month

Coming back to your current issue, would you be able to share a filter box to show the format for Date field?

[#8]

Month is actually "Monthname'', will try to adjust that ...

[#9]

so the Date field is Date and the Month field is Monthname

Date format is DD/MM/YYYY

any idea what the formula would look like (e.g. October -1 obviously goes wrong..)?

thanks again

[sunny_talwar]

Can you check what do you get when you use this in a KPI object

='>=' & Date(MonthStart(Max(Date), -1), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(Date), -1), 'DD/MM/YYYY')

Does the above give you the right date range in the right format? If it does, then this should work for you

Sum({<Month =, Date = {"$(='>=' & Date(MonthStart(Max(Date), -1), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(Date), -1), 'DD/MM/YYYY'))"}>} [#Quantity])

Just added the above KPI object expression within the dollar sign expansion