Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Rolling 12Months for Sales in Bar Chart

Hello Team,

I am trying to show only last 12 months Total_Expense measure value in bar chart... X-Axis = YYYYMM & Y-Axis= Sum of Total Expense.

I am using Sum(TOTAL_EXPENSE) expression in measure value..


Format is YYYYMM, which data used to.

Can any one help me find out expression which i can use for last 12 rolling months in the bar chart..

Untitled.png

27 Replies
YoussefBelloum
Champion
Champion

Sum(  {<YYYYMM={">=$(=AddMonths(Max(Date(Date#(YYYYMM,'YYYYMM'),'YYYYMM')),-12))<=$(=Max(Date(Date#(YYYYMM,'YYYYMM'),'YYYYMM')))"}>}  TOTAL_EXPENSE)


PS: Make sure you use the correct field name (maybe it is not named like YYYYMM)

Anonymous
Not applicable
Author

I am limiting last 12 months only...

X-Axis(Dimension) = CLAIM_YEARMONTH - Format like YYYYMM (Eg: 201701, 201702,.. 201707 .. 201712, 201801, 201802)

Y-Axis(Measure) = TOTAL_EXPENSES - $

in

If is format case, how to set my yearmonth analysis? Becuase there is no date available in the data.. All data's pretending by year & month.. Like YYYYMM

YoussefBelloum
Champion
Champion

Sum(  {<YYYYMM={">=$(=AddMonths(Max(Date(Date#(CLAIM_YEARMONTH,'YYYYMM'),'YYYYMM')),-12))<=$(=Max(Date(Date#(CLAIM_YEARMONTH,'YYYYMM'),'YYYYMM')))"}>}  TOTAL_EXPENSE)

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

You will therefore be wanting something like this in the expression:

Sum(  {<CLAIM_YEARMONTH={">=$(=Date(AddMonths(Max(CLAIM_YEARMONTH),-12),'YYYYMM')"}>}  TOTAL_EXPENSES)

Steve

Anonymous
Not applicable
Author

{">=$(=Date(AddMonths(Max(CLAIM_YEARMONTH),-12),'YYYYMM')"}>}

Why these are in Green color...? while pasting in Expression....

i think i need to make set format for yearmonth like "YYYYMM"? If yes, how should i do it?

YoussefBelloum
Champion
Champion

My bad,

like steve dark said it will be this:

Sum(  {<CLAIM_YEARMONTH={">=$(=AddMonths(Max(Date(Date#(CLAIM_YEARMONTH,'YYYYMM'),'YYYYMM')),-12))<=$(=Max(Date(Date#(CLAIM_YEARMONTH,'YYYYMM'),'YYYYMM')))"}>}  TOTAL_EXPENSE)

Anonymous
Not applicable
Author

No Youssef... Again its showing all the months... not limiting to last 12 months...

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Note you have referred to both TOTAL_EXPENSE and TOTAL_EXPENSES, you will need to use the right name.

It's green because it is set analysis, this is just how Sense formats set analysis.

If CLAIM_YEARMONTH is not actually a date field, rather it's a string or a number, then it will need to be treated differently.

If you create a Filter Pane with CLAIM_YEARMONTH, does it align to the right or the left?

You may be able to get the expression to work with the following:

Sum(  {<CLAIM_YEARMONTH={">=$(=Max(CLAIM_YEARMONTH)-100)"}>}  TOTAL_EXPENSES)

The best thing to do is to test and then build up, for example, does this work:

Sum(  {<CLAIM_YEARMONTH={">=201703"}>}  TOTAL_EXPENSES)


If so, we just need to get the date value to work correctly.  Next create a text box and check the contents of the variable (the bit inside the $() ) to work, add the following as a measure:

=Max(CLAIM_YEARMONTH)-100


Check whether that returns the month you are expecting.

It will be far better to format the date properly on load (using Date and Date#) but treating the date as an integer should work.

Steve

YoussefBelloum
Champion
Champion

would you be able to attach some data here ? at least more than 12 rows corresponding to year using a YYYYMM field

Anonymous
Not applicable
Author

Here i attached raw data which 201601 to 201802... But i need to show last 12 rolling months... Eg: 201703 to 201802...