Need to Get current year and previous month data

[khanashique]

Hi Experts,

I need to see the sales for current year's previous month's data (this works only if i apply filter gauge and then selects the particular month), how to write a correct expression.

By giving below expression i can get the current years data but when i add months expression it gets failed.

Sum({<Year={'2018'}>}YTD_Sales) --> getting current year sales.

Sum({<Year={'2018'},Month={Max(Month)-1)}>}YTD_Sales) --> expression gets failed does not return any value.

Have tried another expression but no result:

Sum({<Year={'2018'},Month={Month(AddMonths(Now()),-1)}>}YTD_Sales) --> expression gets failed does not return any value.

Thanks,

MAK.

[shiveshsingh · Accepted solution]

Try this

Sum({<Year={'2018'},Month={"$(=Month(AddMonths(Now(),-1)))"}>}YTD_Sales)

[vardhancse]

Hi,

can try this for previous month:

Month(AddMonths(Max(Date),-1))

**Date is is your date field you can replace with your actual date field

[khanashique]

Thanks Shivesh, It worked...

[khanashique]

Hi Sasi,

Thanks for your reply --> Month(AddMonths(Max(Date),-1)) did not worked...

The expression which worked is below that is provided by Shivesh

Sum({<Year={'2018'},Month={"$(=Month(AddMonths(Now(),-1)))"}>}YTD_Sales)

Regards,

MAK.

[shiveshsingh]

Glad to know it worked

[vardhancse]

Hi,

I meant replace Date with your date field and then this expression will work.

If you place Date column which is there not there in your data mart then it will not work.

However above expression mentioned correct is correct is correct but it's hard coded, but if we change the month in filter then it may not show the correct value

[khanashique]

Hi Thanks once again,

Yes, i have replaced the Date to my Month Field and it did not worked, yes expression which was provided by Shivesh does not change any value if the month filter is selected...

But the purpose of calculation in expressions solved in summary... (so the expression provided by Shivesh works fine.)

Thanks,

MAK