Qlik Community

Qlik Sense Cloud Discussions

Highlighted
rgreben
Not applicable

Qlik Sense: Current Year vs Prior Year Cumulative Values in a Line Chart

Hello Group!

I am trying to display cumulative values (sales) in a line graph. One line would be for current year and another line would be for prior year. I would like to show all of prior year and whatever is available for current year. My dimension would alternate between fiscal day, fiscal week or fiscal month. I have the following formulas. Prior year formula does not show the full year. How do I adjust it to show the full prior year cumulative? 

Thank you for your help.

Prior Year

rangesum(above(Num(
Sum({<[Fiscal Month]=,[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal MonthYear]=,
[Calendar.Date]={">=$(=(MonthStart((today()), -12)))<=$(=AddMonths(Today(), -12))"}>} NetSales)/1000000

,'$#,##0.0M;($#,##0.0M)')
,0,rowno()))

Current Year

rangesum(above(Num(
sum({<[Fiscal Month]=,[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal MonthYear]=,[Calendar.Date]={">=$(=MonthStart(Today()))<=$(=Today())"}
>}NetSales)/1000000

,'$#,##0.0M;($#,##0.0M)')
,0,rowno()))

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Qlik Sense: Current Year vs Prior Year Cumulative Values in a Line Chart

Ya that I wouldn't know about until I see it Smiley Happy

10 Replies
sunny_talwar
Not applicable

Re: Qlik Sense: Current Year vs Prior Year Cumulative Values in a Line Chart

Are you looking to MTD accumulation or YTD accumulation? Currently, you expression seems to be doing MTD accumulation but since  we are in Jan, and MTD and YTD are the same... it may not matter, but starting Jan, you might see different numbers... what exactly are you looking for?

rgreben
Not applicable

Re: Qlik Sense: Current Year vs Prior Year Cumulative Values in a Line Chart

Thank you for your response. I am looking for YTD accumulation. I would like to see Full Prior Year Accumulation regardless of where we are at in the Current Year. For Current Year, I would like to see YTD accumulation.

sunny_talwar
Not applicable

Re: Qlik Sense: Current Year vs Prior Year Cumulative Values in a Line Chart

Can you try these

Prior Year

 

Num(RangeSum(Above(
Sum({<[Fiscal Month], [Fiscal Year], [Fiscal Quarter], [Fiscal MonthYear],
[Calendar.Date] = {">=$(=YearStart(Today(), -1))<=$(=AddYears(Today(), -1))"}>} NetSales)/1000000
, 0, RowNo())), '$#,##0.0M;($#,##0.0M)')

Current Year

Num(RangeSum(Above(
Sum({<[Fiscal Month], [Fiscal Year], [Fiscal Quarter], [Fiscal MonthYear],
[Calendar.Date] = {">=$(=YearStart(Today()))<=$(=Today())"}>} NetSales)/1000000
, 0, RowNo())), '$#,##0.0M;($#,##0.0M)')
rgreben
Not applicable

Re: Qlik Sense: Current Year vs Prior Year Cumulative Values in a Line Chart

Thank you for the response. I am only seeing January values for CY and PY. I would like to see Full Prior Year.

sunny_talwar
Not applicable

Re: Qlik Sense: Current Year vs Prior Year Cumulative Values in a Line Chart

Try this for Prior Year

Num(RangeSum(Above(
Sum({<[Fiscal Month], [Fiscal Year], [Fiscal Quarter], [Fiscal MonthYear],
[Calendar.Date] = {">=$(=YearStart(Today(), -1))<$(=YearStart(Today()))"}>} NetSales)/1000000
, 0, RowNo())), '$#,##0.0M;($#,##0.0M)')
rgreben
Not applicable

Re: Qlik Sense: Current Year vs Prior Year Cumulative Values in a Line Chart

Thank you for your response. The formula seems to be working. However, for current year, the cumulative value for January carries into future months; meaning, if January cumulative is $100M then all future months show up with a cumultaive of $100M.  Is there any way to not show cumulative for future months; meaning, only show January cumulative this month and January and February next month, etc?

sunny_talwar
Not applicable

Re: Qlik Sense: Current Year vs Prior Year Cumulative Values in a Line Chart

May be try this

Num(RangeSum(Above(
Sum({<[Fiscal Month], [Fiscal Year], [Fiscal Quarter], [Fiscal MonthYear],
[Calendar.Date] = {">=$(=YearStart(Today()))<=$(=Today())"}>} NetSales)/1000000
, 0, RowNo())), '$#,##0.0M;($#,##0.0M)')

*

Avg({<[Fiscal Month], [Fiscal Year], [Fiscal Quarter], [Fiscal MonthYear],
[Calendar.Date] = {">=$(=YearStart(Today()))<=$(=Today())"}>} 1)
rgreben
Not applicable

Re: Qlik Sense: Current Year vs Prior Year Cumulative Values in a Line Chart

Thank you for your response. While there are no future cumulative for future months and the values look correct, my prior year is off. It looks like I am pulling in extra days which makes my prior year sales higher than they should be.

sunny_talwar
Not applicable

Re: Qlik Sense: Current Year vs Prior Year Cumulative Values in a Line Chart

Ya that I wouldn't know about until I see it Smiley Happy