Sales for Rolling 12 months from current date

[Nandini.t]

Hello all,

I have sales and yearmonth fields. I want to calculate Sales  for rolling 12 months from the current date.

Please help me.

Thanks

[BigChris]

And what have you tried so far?

[Nandini.t]

[Total Sales] between (to_char(_add_months(current_date,-12),'YYYYMM')) and (to_char(_add_months(current_date,-1),'YYYYMM'))

But this is not working.throwing the below error
The operation "greater_equal" is invalid for the following combination of data types: "quadword" and "varchar"RSV-SRV-0042 Trace back:RSReportService.cpp(717): QFException: CCL_CAUGHT: RSReportService::processImpl()

[Nandini.t]

any views on this...? would be highly appreciated..

[Lynn]

[Total Sales] between (to_char(_add_months(current_date,-12),'YYYYMM')) and (to_char(_add_months(current_date,-1),'YYYYMM'))

But this is not working.throwing the below error
The operation "greater_equal" is invalid for the following combination of data types: "quadword" and "varchar"RSV-SRV-0042 Trace back:RSReportService.cpp(717): QFException: CCL_CAUGHT: RSReportService::processImpl()

It isn't clear to me what this expression represents or what you actually need. Is this a query item in your query or is it a filter? It looks like your source is a relational Oracle database, is that the case? Are you looking to get sales by month for the R12 period or do you just want the total figure for the period?

What is [Total Sales]? Is that your measure? If so, why are you comparing it to dates that are transformed to represent month and year only? Is there a date field in your source?

If you filter your query like so:

[YourDateField] between _add_months(current_date,-12) and _add_months(current_date,-1)

Then you would just include your [TotalSales] metric in the query and let it aggregate to show the R12 sales amount because your filter has restricted the result set to the desired period.

Of course I don't know if I'm correct in my assumptions here but more detail from you could yield better answers.

[Nandini.t]

Yes Lynn your assumptions are correct.

actually i have a list with Yearmonth(grouped,which is a date range parameter),customer number,name,monthly sales,avg monthly slaes, GP, 12 month rolling , rolling 12 month sales ,average of rolling 12 months ales.

if i just drag total sales after 12 months caluclated field i am geeting monthly sales itself not 12 months rolling sales.

I feel i need to have 2 queries for both monthly & 12 months rolling and i need to join these to to put it in a single list?Please guide

[Nandini.t]

to add. the expression is not filter . it is a data item.

[Lynn]

Yes Lynn your assumptions are correct.

actually i have a list with Yearmonth(grouped,which is a date range parameter),customer number,name,monthly sales,avg monthly slaes, GP, 12 month rolling , rolling 12 month sales ,average of rolling 12 months ales.

if i just drag total sales after 12 months caluclated field i am geeting monthly sales itself not 12 months rolling sales.

I feel i need to have 2 queries for both monthly & 12 months rolling and i need to join these to to put it in a single list?Please guide

Your list describes the required output you need, correct? Can you describe what elements you have in your source? There must be a date item in there and presumably a sales metric at that level of granularity?

In any case, you need to understand that filters must evaluate to a true or false result whereas a query item expression must evaluate to some value. If you filter your query for the rolling 12 month period then you can have a query item for monthly sales that contains a case statement to evaluate the dates on the source and if it is in the right range then include the value or else make it zero.

Then your R12 query item would just include all the sales values since your query is already filtered only for that range. Of course you've only described your desired result (I think) without really explaining what your underlying source query items are, so consider this suggestion while bearing in mind that I'm guessing. Hopefully you can adapt the technique to whatever your situation calls for.

The monthly expression for the current MTD period might be:

Code Select Expand


case
  when [YourDateField] >= _first_of_month(current_date)
  then [YourSalesMetric]
  else 0
end