YTD calculation for all the years in Qliksense Bar chart

[abhijith28]

Hi,

I have to calculate YTD for all the particular previous & current years in Qlik bar chart

Input data:

Expected output:

Calculation should be YTD , even for previous years only YTD months should be used

for ex: for 2021-  sum of values will be from Jan 2021 - Oct 2021 

              for 2020- sum of values will be from  Jan 2020 -Oct 2020

[RafaelBarrios]

hi @abhijith28 

i would do this

first
create a new column in your script as your month is text and not date format

LOAD 
...
date(date#(month,'MMM'),'M') as month_number
...

 your should get something like

Second, your set analysis formula for expression

sum({< month_number={"<=$(=num(month(today())))"}>} value)

in your Expected output from 2021 you are excluding Oct

if so, try

sum({< month_number={"<=$(=num(month(addmonths(today(),-1))))"}>} value)

hope this helps.

best,

[abhijith28]

Thanks @RafaelBarrios 

I need a output in pivot table or bar chart where one dimension year and one expression.

The expression which you have shared wont work for all the years. 

[sidhiq91]

@abhijith28  Could you please try something like below in the back end:

NoConcatenate
Temp:
Load Product,
Date(Date#(Year,'YYYY'),'YYYY') as Year,
Date(Date#(Month,'MMM'),'MM') as Month,
Value
inline [
Product, Year, Month, Value
A,2022, Jan, 10
A,2022, Feb, 20
A,2022, Mar, 30
B,2022, Jan, 25
B,2021, Feb, 50
B,2021, Sep, 30
B,2021, Oct, 10
C,2020, Mar, 25
C,2020, Apr, 30
C,2020, Nov, 40
];

Exit Script;

For Current Year: Sum({<Year={"$(=Year(today()))"}, Month={">=$(=Date(Yearstart(today()),'MM'))
<=$(=Date(monthstart(today()),'MM'))"}>} Value)

For previous Year: Sum({<Year={"$(=Year(today())-1)"}, Month={">=$(=Date(Yearstart(today()),'MM'))
<=$(=Date(monthstart(today()),'MM'))"}>} Value)

For Year before that: Sum({<Year={"$(=Year(today())-2)"}, Month={">=$(=Date(Yearstart(today()),'MM'))
<=$(=Date(monthstart(today()),'MM'))"}>} Value)

You can use these expression in the Pivot Chart: