Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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.
@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: