topic Re: exclude current month in Visualization and Usability

exclude current month

joeybird — Fri, 09 Oct 2015 10:31:32 GMT

Hiya

have a kpi

I am trying to write something like this

sum({$< Order_Date -={$(Month(getdate()))} >}Customer_ID)

I wish to exclude this months 'October 2015' (When this post was written) orders for customers

where am I going wrong

please help

Re: exclude current month

Fri, 09 Oct 2015 10:34:51 GMT

try <MonthStart(Today())

instead of -=Month(getdate())

Re: exclude current month

tresB — Fri, 09 Oct 2015 10:37:59 GMT

You can't compare date field with month value. Rather, create a month field in the script (if already not there) and try expression like:

Load

Month(Oredr_Date) as Month,

....

sum({$< Month -={$(=Month(today()))} >}Customer_ID)

Re: exclude current month

Kushal_Chawda — Fri, 09 Oct 2015 10:46:07 GMT

try

=sum({$< Order_Date ={"<$(=monthstart(max(Order_Date )))"} >}Customer_ID)

Re: exclude current month

joeybird — Fri, 09 Oct 2015 10:53:49 GMT

Hiya

brill thanks Tresesco !

and team too

Kind Regards

Re: exclude current month

joeybird — Fri, 09 Oct 2015 12:38:16 GMT

Have got additional issue

sum({$< Month -={$(=Month(today()))} >}Customer_ID)

how can this be altered to show for previous months?

so plot total against each month of the year?

please help

Re: exclude current month

tresB — Fri, 09 Oct 2015 12:51:19 GMT

Try like:

Dimension: Month

Exp: sum({$< Order_Date ={'$(>=YearStart(Today())<=MonthEnd(Today(),-1))'} >}Customer_ID)

Re: exclude current month

joeybird — Fri, 09 Oct 2015 13:36:37 GMT

Hiya

no joy with the above, any more ideas

just get -

please help

Re: exclude current month

Fri, 09 Oct 2015 13:41:19 GMT

sum({$< Order_Date ={'$(>=YearStart(Today())<=AddMonths(MonthEnd(Today()),-1)))'} >}Customer_ID)

think i've got all the brackets in the right places!

Re: exclude current month

joeybird — Fri, 09 Oct 2015 14:57:53 GMT

hiya

I think its an issue with my master calendar

need to re-arrange this please some how

I have come close with this

count({$< [Order_Date.Calendar.Month] ={$(=Month(today()))} >}Customer_ID) e.g I get 10

the logic would say count({$< [Order_Date.Calendar.Month] -={$(=Month(today()))} >}Customer_ID) but I get 0

please help

Re: exclude current month

Fri, 09 Oct 2015 15:04:07 GMT

count({$< [Order_Date.Calendar.Month] ={$(=Month(AddMonths(today(),-1)))} >}Customer_ID)

AddMonths(today(),-1) will give you last month.

caution though - the Month function will take out the Year portion - so if you have more than a years worth of data, it will look at multiple Septembers....

Re: exclude current month

joeybird — Mon, 12 Oct 2015 13:20:43 GMT

Hiya

this still returns the value of 0

I try this, but it does not seem to minus order dates for this month

COUNT({$< Order_Date -= {$(=Month(today()))}>}Customer_ID)

what can be wrong?

please help

Re: exclude current month

Mon, 12 Oct 2015 15:12:33 GMT

hello

does:

COUNT({$< Order_Date <= {$(=AddMonths(MonthEnd(today()),-1))}>}Customer_ID)

work?

Re: exclude current month

joeybird — Tue, 13 Oct 2015 13:11:37 GMT

Brill, thanks again, it worked x

Kind Regards