Year to date & Month to date

[cedfoning]

Hello, 
I have an issue, 

i have the following table : 

I am new to Qlik and i want to have the following calculated files : 

1)YTD, 

2) Sum of amount for the since the beginning of the year 2022 (without the Yearmonth 202205)

3) Last year YTD

It will be helpful if anybody can help

@Taoufiq_Zarra 

[vinieme12 · Accepted solution]

As below

2) Sum of amount for the since the beginning of the year 2022 (without the Yearmonth 202205) ,, not sure can you confirm exactly what you need

Then use the flags in your expressions 

YTD - Current YEar = sum({<YTD_currentYear={1}>}Amount)

YTD - Last YEar = sum({<YTD_lastYear={1}>}Amount)

Fact:
Load *,monthstart(date#(YrMnth,'YYYYMM')) as tDate Inline [
YrMnth,Amount
202201,1298
202202,1276
202203,934
202204,890
202205,1750
202101,2965
202102,1022
202103,1580
202104,1222
202105,1101
202106,2750
202107,265
202108,1783
202109,488
202110,2390
202111,630
202112,2520
];

Calendar:
Load
TempDate as tDate
,InYearToDate(TempDate, maxdate,0,1)*-1 as YTD_currentYear
,InYearToDate(TempDate, maxdate,-1,1)*-1 as YTD_lastYear
;
LOAD
date(MonthStart(mindate ,IterNo()-1)) AS TempDate
,maxdate
WHILE monthstart(mindate , IterNo()-1) <= maxdate;
LOAD
date(min(FieldValue('tDate', recno()))) as mindate
,date(max(FieldValue('tDate', recno()))) as maxdate
AUTOGENERATE FieldValueCount('tDate');

exit Script;

[cedfoning]

Hello, thanks for your reply 

for the second question, I want : 

2) Sum(amount) for 2022, but without the amount of the Yearmonth 202205)

and for the 3rd question, the exact same, but for the year 2021

thanks

[vinieme12]

just modify the below, that's all

,InYearToDate(TempDate, maxdate,0,1)*-1 as YTD_currentYear

,InYearToDate(TempDate, addmonths(maxdate,-1),0,1)*-1 as YTD_exceptlatestMonth

,InYearToDate(TempDate, maxdate,-1,1)*-1 as YTD_lastYear

,InYearToDate(TempDate,addmonths(maxdate,-1),-1,1)*-1 as YTD_lastYearexceptLatestMonth

[cedfoning]

Hello, 
i have another issue

Now, i want to analyse only the month of may of each year.
How can i do that ? 

Thanks

[vinieme12]

Dimension = Year

Measure

=sum({<Month={"=$(=Month(Max(Datefield)))"}>}Amount)

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

[cedfoning]

thanks for your reply, 

it does not work
it shows me the values for all the year, not for the month only. 

[vinieme12]

Can you post a screenshot of the expression editor and also confirm the format of the Month field

refer the below link, 

Sum( {$<Month={"$(=Date(Max(Month),'MMM-YY'))"}>} Amount )

change format MMM-YY to match your month field

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

[cedfoning]

Hello, the need is as follow : 

if i filter on the month of april, i want to have the values from january to april for each year. 

if i filter on the month of may, i want to have values from january to may for each year. 

Is it possible ? 

[vinieme12]

As below

Current year YTD

Sum( {<Datefield={">=$(=Date(yearstart(Max(Datefield)),'YYYY-MM-DD')<=$(=Date(Max(Datefield),'YYYY-MM-DD')"}>} Amount )

PRevious year YTD

Sum( {<Datefield={">=$(=Date(yearstart(Max(Datefield),-1),'YYYY-MM-DD')<=$(=Date(addyears(Max(Datefield),-1),'YYYY-MM-DD')"}>} Amount )

replace Datefield with the date field name in your app

also replace  YYYY-MM-DD with the date format of the date field as in your app