How to show sales for the month in text object

[ashis]

Hello,

I want to show sales for selected month and previous month in two object objects.

I have month field in a list box.

I am writing the following set expression however not getting any data:

=sum({<Month={$(=Max(Month))}>}Sales)  and

=sum({<Month={$(=Max(Month)-1)}>}Sales)

Month list box lists the months as Jan, Feb, Mar...etc.

Also,If i want to show selected month and its previous month in text object what would be the express.

I tried the following expression it seems not working

vCurrentMonth=date(date#(Max(Month),'YYY'),'YYY')

vPreviousMonth=date(date#(Max(Month)-1,'YYY'),'YYY')

Please suggest.

Thank you,

[#2]

Hi

consider this for present month (Datess denotes the date field)

Sum({<Datess ={'>=$(=MonthStart(max(Datess))) <=$(=max(Datess))'}>} Sales)

for previous month,

Sum({<Datess ={'>=$(=MonthStart(max(Datess),-1)) <=$(=MonthEnd(max(Datess))-1)'}>} Sales)

[PrashantSangle]

Try in double quote

like

=sum({<Month={"$(=Max(Month))"}>}Sales)  and

=sum({<Month={"$(=Max(Month)-1)"}>}Sales)

If it is string data then use maxstring() instead of max()

Regards,

[ashis]

Hi Prashant,

Thank you for your reply.

the following expression is working

=sum({<Month={"$(=Maxstring(Month))"}>}Sales)

however the previous months sales in not coming using

=sum({<Month={"$(=Maxstring(Month)-1)"}>}Sales)

Thank you,

Ashis

[arvind_patil]

Hi Ashish,

For Previous Month Use Below:

=sum({<Month={"$(= Addmonths(Max(Month))-1)"}>}Sales)

Thanks,

Arvind Patil

[techvarun]

Try this.

LOAD *,if(Isnull(peek(Sales)),0,peek(Sales)) as LastMonthSales;

LOAD * INLINE [

    Month, Sales

    Jan, 12

    Feb, 14

    Mar, 16

    Apr, 18

    May, 27

    Jun, 41

    Jul, 61

    Aug, 91

    Sep, 137

    Oct, 205

    Nov, 308

    Dec, 461

];

[techvarun]

qvw File attached

[ashis]

Hi Arvind,

=sum({<Month={"$(= Addmonths(Max(Month))-1)"}>}Sales) is not giving me any error in expression , however I am getting 0.0 0 as value.

Thank you,

Ashis

[Kushal_Chawda]

what is the format of your Month field

[avinashelite]

Convert the "$(=Maxstring(Month)-1) to the Month format on the left hand side ....if your not able to achieve please share the sample app