Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
WernerDC
Creator
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
stevejoyce
Specialist II
Specialist II

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

If(Date <= selectedDate, 

  yourexpression

)

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

WernerDC
Creator
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.

 

Please advise

stevejoyce
Specialist II
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()))

)

WernerDC
Creator
Creator
Author

This lets my line disappear.

stevejoyce
Specialist II
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)'

WernerDC
Creator
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