# App Development

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Creator

## Accumulated Line Chart YTD

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

7 Replies
Specialist II

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

If(Date <= selectedDate,

yourexpression

)

Creator
Author

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

Creator
Author

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.

Specialist II

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

)

Creator
Author

This lets my line disappear.

Specialist II

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

Creator
Author

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

Tags
Community Browser