Set Expression Month Condition

[khanashique]

Hi Experts,

I am facing an issue in the following looking numbers for last month, while adding the set expression in graph:

Dimension = Date

Measure = Sum({<Category = {'Books'},Month = {"$(=Month(Today())-1)"}>}OrderQty)

Thanks,

MAK.

[Channa]

if you looking for last month

then  vMonth =$(vYearStart_Join)

Measure = Sum({<Category = {'Books'},Month = {"$(vMonth )"}, year ={"=year(today())"}>}OrderQty)

try this if you look for current year then 

[khanashique]

I am looking for current year last month

[Channa]

ok

create variable

vYear= year(today())

Measure = Sum({<Category = {'Books'},Month = {"$(vMonth )"}, year ={"$(vYear)"}>}OrderQty)

[Channa]

if you have date in you date base

create these two variable

vMonthStart=monthstart(AddMonths(Today(),-1))

vMonthEnd=monthend(AddMonths(Today(),-1))

Measure = Sum({<Category = {'Books'},date ={">=$(vMonthStart)<=$(vMonthEnd)"}>} >}OrderQty)

if you have date it is better when you are in jan it will take to december last year

[khanashique]

the above expressions/variable does not work.

I am sure with the below expression, but donno why its not working:

Measure = Sum({<Category = {'Books'},Month = {"$(=Month(Today())-1)"}>}OrderQty)

[Channa]

did you tri passing 

=Month(Today())-1

into variable

or else check your Month data format, may be in your DB you have month name

[pradosh_thakur]

hi @khanashique 

Your expression is good and shall work. Can you tell us how your month field looks like? What are the values? And When you say it is not working what is wrong ? A screenshot will help as well.

-Pradosh

[khanashique]

Hi Pradosh,

[khanashique]

Please see attached data