topic Re: YTD expression not working in KPI in App Development

YTD expression not working in KPI

Aspiring_Developer — Sat, 16 Nov 2024 01:58:23 GMT

Hello Everyone,

Please help me on the below situation:

I am calculating YTD in a KPI

Feild:- Date.MonthEnd

Formaula

Sum({<Indicateur.Code = {'PO.2.24'},[Date.MonthEnd]={">=$(=Num(YearStart(max(Date.MonthEnd))))<=$(=num(Max(Date.MonthEnd))))"}>}[Indicateur.Value])

It gives me the correct total value for all the years starting from jan 2020 till Apr2020 as i have data till Apr

But when i select any month from Date.MonthEnd Filter is not showing the cumulative value lie

Jan2020 50

Feb 2020 50

So, KPI should show me 100 when i select Feb 2020 . however, the KPi is Populating the value for individual months.

Please help.

Thanks in advance.

Re: YTD expression not working in KPI

felcar2013 — Mon, 06 Jul 2020 13:20:22 GMT

you use the same field for both the expression and the filter. When you select your field value, you have defined a new selection state and that is why you see only the value you selected and not the cumulative one.

You could create an index in your calendar and use a flag InYearToDate . Use this flag in your expression and use your "Date.MonthEnd" field as you did, as a filter.

hope it helps

Re: YTD expression not working in KPI

Aspiring_Developer — Mon, 06 Jul 2020 13:33:34 GMT

Can you please explain more ?

How should i create flag in calendar . This is my calendar in data model.

TMPDATE:
load

Min(%Key_Date) as MinDate,
Max(%Key_Date) as MaxDate
resident Data;

LET vMinDate = floor(peek('MinDate'));
LET vMaxDate = floor(peek('MaxDate'));

drop table TMPDATE;

ChampDate:
LOAD date($(vMinDate)+IterNo()-1) AS MaDate
AUTOGENERATE (1)
WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

// creation of the different components of our calendar
Calendar:
Calendar:
LOAD

MaDate as %Key_Date,
MaDate as Date,
Month(MaDate) as Date.Month,
num(Month(MaDate)) as Date.NumMonth,
MonthName(MaDate) as Date.MonthName,
MonthName(MaDate,-12) as Date.PreviousYearMonthName,
MonthName(MaDate,-1) as Date.PreviousMonthName,
MonthName(MaDate,+1) as Date.NextMonthName,
MonthName(MaDate,+2) as [Date.NextMonthName+1],
Year(MaDate) as Date.Year,
QuarterName(MaDate) as Date.QuarterName,
'Q'&ceil(num(Month(MaDate))/3)& ' ' & Year(MaDate) as Date.Quarter,
num(monthend(MaDate)) as Date.MonthEnd,
num(monthend(addmonths(MaDate,+1))) as Date.NextMonthEnd,
num(monthend(addmonths(MaDate,+2))) as [Date.NextMonthEnd+1],
num(monthend(addmonths(MaDate,-1))) as Date.PreviousMonthEnd,
num(monthend(addmonths(MaDate,-12))) as Date.PreviousYearMonthEnd,
num(monthend(YearStart(MaDate))) as Date.YearStart,
num(monthend(YearStart(AddYears(MaDate,-1)))) as Date.PreviousYearStart

Resident ChampDate;
drop table ChampDate;
//Delete the Data table that we used to retrieve the% Key_Date
DROP Table Data;

And also is there any other alternative to this ?

thanks in advance

Re: YTD expression not working in KPI

Vegar — Mon, 06 Jul 2020 14:16:24 GMT

Could it be that your are making selections in another calendar field than just [Date.MonthEnd]? If so then you need to cancel that selection with a modifier in your set. Like I've done for two of your calendar dimensions below.

Sum({<Indicateur.Code = {'PO.2.24'}, [Date.Month] =, [Date.MonthName]=,[Date.MonthEnd]={">=$(=Num(YearStart(max(Date.MonthEnd))))<=$(=num(Max(Date.MonthEnd))))"}>}[Indicateur.Value])

Re: YTD expression not working in KPI

felcar2013 — Mon, 06 Jul 2020 14:39:10 GMT

hi,

you can add in your calendar a new row, like this:

....................................
num(monthend(YearStart(MaDate))) as Date.YearStart,
num(monthend(YearStart(AddYears(MaDate,-1)))) as Date.PreviousYearStart,

If(InYearToDate(date([YOUR_DATE]),date(Today()),0,1)=-1,1,0) AS Date.YearToDate_flag,

Resident ChampDate;
drop table ChampDate;
//Delete the Data table that we used to retrieve the% Key_Date
DROP Table Data;

##Base Date would be any day of this year and period = 0

Check Syntax: https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunctions/inyeartodate.htm

InYearToDate (timestamp, base_date, period_no[, first_month_of_year])

Then, you add this flag in your expression on the user interface.

Date.YearToDate_flag, = {1} means the value lies in this year.

In your expression you need to ignore other date related fields

Re: YTD expression not working in KPI

Aspiring_Developer — Tue, 07 Jul 2020 10:03:26 GMT

Hello,

Thank you for your response.

Is there any other way out for this ?

I am not able to understand this one.

Re: YTD expression not working in KPI

Aspiring_Developer — Tue, 07 Jul 2020 10:03:56 GMT

Hello,

I didn't worked.

Re: YTD expression not working in KPI

Aspiring_Developer — Tue, 07 Jul 2020 11:28:43 GMT

@felcar2013

Hi,

I was trying to add the Flag row in the calendar and storing the table in qvd but when i load the qvd, i do not get the field

Date.YearToDate_flag in the table. Don't know why it is not creating any field.

Please suggest.

Thanks

Re: YTD expression not working in KPI

felcar2013 — Tue, 07 Jul 2020 13:47:17 GMT

Hi,

try this calendar (user your own dates). I have no qlik sense to share, but add the calendar, don't use the automated one. This calendar starts on 1. Jan 2019, ends Today(). The flag is included. You can test it in your own app, just copy the script in a new app an run it, create a table and you will see that the flag always indicate 1 for 2020. You should use this field in your expression. (Date.YearToDate_flag). Better post some test data or app.

/*** Year to Date ***/

//Set start and end dates
SET vMinDate = 43466;
LET vMaxDate = num(Today());

tmpCalendar:
LOAD date($(vMinDate)+IterNo()-1) AS MaDate
AUTOGENERATE (1)
WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

// creation of the different components of our calendar
Calendar:
LOAD
MaDate as %Key_Date,
MaDate as Date,
Month(MaDate) as Date.Month,
num(Month(MaDate)) as Date.NumMonth,
MonthName(MaDate) as Date.MonthName,
MonthName(MaDate,-12) as Date.PreviousYearMonthName,
MonthName(MaDate,-1) as Date.PreviousMonthName,
MonthName(MaDate,+1) as Date.NextMonthName,
MonthName(MaDate,+2) as [Date.NextMonthName+1],
Year(MaDate) as Date.Year,
QuarterName(MaDate) as Date.QuarterName,
'Q'&ceil(num(Month(MaDate))/3)& ' ' & Year(MaDate) as Date.Quarter,

//Field in filter
num(floor(monthend(MaDate))) as Date.MonthEnd,
//Year To Date Flag
If(InYearToDate(date(MaDate),date(Today()),0,1)=-1,1,0) AS Date.YearToDate_flag,

num(monthend(addmonths(MaDate,+1))) as Date.NextMonthEnd,
num(monthend(addmonths(MaDate,+2))) as [Date.NextMonthEnd+1],
num(monthend(addmonths(MaDate,-1))) as Date.PreviousMonthEnd,
num(monthend(addmonths(MaDate,-12))) as Date.PreviousYearMonthEnd,
num(monthend(YearStart(MaDate))) as Date.YearStart,
num(monthend(YearStart(AddYears(MaDate,-1)))) as Date.PreviousYearStart

Resident tmpCalendar;
drop table tmpCalendar;