topic Accumulated Line Chart YTD in App Development

Accumulated Line Chart YTD

WernerDC — Thu, 05 Aug 2021 14:24:49 GMT

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

Re: Accumulated Line Chart YTD

stevejoyce — Thu, 05 Aug 2021 14:37:39 GMT

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

If(Date <= selectedDate,

yourexpression

)

Re: Accumulated Line Chart YTD

WernerDC — Fri, 06 Aug 2021 07:39:35 GMT

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()))

Re: Accumulated Line Chart YTD

WernerDC — Fri, 06 Aug 2021 09:58:00 GMT

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

Re: Accumulated Line Chart YTD

stevejoyce — Fri, 06 Aug 2021 11:14:51 GMT

In your current year calculation, what if you do

If(dateEntry_det <= $(vDateEnd),

)

Re: Accumulated Line Chart YTD

WernerDC — Fri, 06 Aug 2021 15:58:47 GMT

This lets my line disappear.

Re: Accumulated Line Chart YTD

stevejoyce — Fri, 06 Aug 2021 16:22:09 GMT

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)'

Re: Accumulated Line Chart YTD

WernerDC — Tue, 10 Aug 2021 06:37:01 GMT

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