Accumulated Line Chart YTD

[WernerDC]

Hi guys. Hope someone can help me.

I need to create an Accumulated Line chart of Hectares/Acres work done per week for a Financial year. 

This is what is needed to be on the graph:

1. YTD Hectares/Acres done vs

2. Prior Year YTD done.

It seems like the accumulated function in Sense doesn't allow to stop YTD as per my date selection in my variable input.

Screenshot shows the YTD value from the current week onward. I need the YTD line to stop but the Prior year line to continue its course so that we can see how we are doing against last year.

Hope it all makes sense.

Thank you anyway

[stevejoyce]

What if you wrap your CY YTD with an if-statement date condition...

If(Date <= selectedDate, 

  yourexpression

)

[WernerDC]

This is my formula: How do I get it to stop with my date range selected?

RangeSum(Above(If(Count([$(=Replace(GetObjectField(0),']',']]'))]) > 0, (　If(country_det = 'USA' ,sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyAcre_det),
If(country_det = 'ZA' ,sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyHa_det),
If(country_det = 'AUS' ,sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyHa_det),
If(country_det = 'NZ' ,sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyHa_det),
If(country_det = 'RoA' ,sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyHa_det), sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyHa_det))))))　) + Sum({1<[$(=Replace(GetObjectField(0),']',']]'))]={"=Only({1}[$(=Replace(GetObjectField(0),']',']]'))])>='$(=MinString([$(=Replace(GetObjectField(0),']',']]'))]))' and Only({1}[$(=Replace(GetObjectField(0),']',']]'))])<='$(=MaxString([$(=Replace(GetObjectField(0),']',']]'))]))'"}>}0), 0), 0, RowNo()))

[WernerDC]

Guys.

Green line is current year

Orange line is Prior year. 

I need the prior year to continue and the current year to stop YTD.

Please advise

[stevejoyce]

In your current year calculation, what if you do 

If(dateEntry_det <= $(vDateEnd),

RangeSum(Above(If(Count([$(=Replace(GetObjectField(0),']',']]'))]) > 0, (　If(country_det = 'USA' ,sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyAcre_det),
If(country_det = 'ZA' ,sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyHa_det),
If(country_det = 'AUS' ,sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyHa_det),
If(country_det = 'NZ' ,sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyHa_det),
If(country_det = 'RoA' ,sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyHa_det), sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={30}>} qtyHa_det))))))　) + Sum({1<[$(=Replace(GetObjectField(0),']',']]'))]={"=Only({1}[$(=Replace(GetObjectField(0),']',']]'))])>='$(=MinString([$(=Replace(GetObjectField(0),']',']]'))]))' and Only({1}[$(=Replace(GetObjectField(0),']',']]'))])<='$(=MaxString([$(=Replace(GetObjectField(0),']',']]'))]))'"}>}0), 0), 0, RowNo()))

)

[WernerDC]

This lets my line disappear.

[stevejoyce]

Hm, are your filter selections filtering dateEntry_det? If so you'll need to ignore similar to your expression set analysis or ignore all filter like If(only({1} dateEntry_det), ... 

Or did you try dateEntry_det <= '$(vDateEnd)'

[WernerDC]

Hi,

I changed my formula to this.

Variables:

SET [accum] = "RangeSum(Above(";
SET [/accum] = ",0,RowNo()))";

This is Current YTD (I want it to stop at current day)

$(accum)
If(country_det = 'USA' ,sum({<dateEntry_det={">=$(vDateStart)"} ,[serviceCode_det]={30}>} qtyAcre_det),
If(country_det = 'ZA' ,sum({<dateEntry_det={">=$(vDateStart)"} ,[serviceCode_det]={30}>} qtyHa_det),
If(country_det = 'AUS' ,sum({<dateEntry_det={">=$(vDateStart)"} ,[serviceCode_det]={30}>} qtyHa_det),
If(country_det = 'NZ' ,sum({<dateEntry_det={">=$(vDateStart)"} ,[serviceCode_det]={30}>} qtyHa_det),
If(country_det = 'RoA' ,sum({<dateEntry_det={">=$(vDateStart)"} ,[serviceCode_det]={30}>} qtyHa_det),
sum({<dateEntry_det={">=$(vDateStart)"},[serviceCode_det]={30}>} qtyHa_det))))))$(/accum)　

This is my Prior year. I want this line to continue to the end of our financial year. (FYear: 2021/03/01-2022/02/28)

$(accum)
If(country_det = 'USA' ,sum({<dateEntry_det={">=$(vDateStart)"} ,[serviceCode_det]={30}>} qtyAcre_det_H),
If(country_det = 'ZA' ,sum({<dateEntry_det={">=$(vDateStart)"} ,[serviceCode_det]={30}>} qtyHa_det_H),
If(country_det = 'AUS' ,sum({<dateEntry_det={">=$(vDateStart)"} ,[serviceCode_det]={30}>} qtyHa_det_H),
If(country_det = 'NZ' ,sum({<dateEntry_det={">=$(vDateStart)"} ,[serviceCode_det]={30}>} qtyHa_det_H),
If(country_det = 'RoA' ,sum({<dateEntry_det={">=$(vDateStart)"} ,[serviceCode_det]={30}>} qtyHa_det_H),
sum({<dateEntry_det={">=$(vDateStart)"},[serviceCode_det]={30}>} qtyHa_det_H))))))$(/accum)　

Kind regards