FTD,MTD,YTD (Qlik Sense)

[satish25]

hi,

please help me out to find the fields FTD,MTD,YTD.

the date field is FKDAT(taking from sap) and i have linked this with normal Calendar Date as follows

CalendarMaster:

LOAD

    Date(FKDAT) AS FKDAT,

    Floor(Date(FKDAT)) AS DateNum,

    Year(FKDAT) AS Year,

    'Q' & Ceil(Month(FKDAT) / 3) AS Quarter,   

    Month(FKDAT) As Month,

    Day(FKDAT) As Day,

    Week(FKDAT) As Week

RESIDENT SAP_Data;

how to find FTD,MTD,YTD for (Sum(Sales)) using set expression .

FTD-  for the day-1

MTD- for the prevoius month

(if current year is January 2017 the it should be show sum of December 2016).

i have to display FTD,MTD,YTD in Table

[pradosh_thakur · Accepted solution]

Hi Satish

not sure what you want is really called MTD or YTD or more like previous month sale or previous day sale

but the expressions will look more or less like this, Please check your date format

set vMaxDate = =Date(max(FKDAT), 'DD-MM-YYYY');

set vPrevDate = =Date(max(FKDAT)-1, 'DD-MM-YYYY');

set vPrevMonth = =Date(MonthStart(Max(FKDAT), -1), 'MMM')

expressions

Sum({<[FKDAT]={'$(vMaxDate)'},Year,Month>}Sales)

SUM({<Month={'$(vPrevMonth)',Year>}Sales)

[insurancenewsnepal]

To find FTD (for the day-1), MTD (for the previous month), and YTD (Year-to-Date) for Sum(Sales) using set expressions in QlikView, you can create calculated expressions in your table. Here's how you can achieve that:

1. Calculate FTD (for the day-1):

Sum({<DateNum = {"$(=Date(DateNum-1))"}>} Sales)

In this set expression, we use the `DateNum` field to filter for the previous day (FTD).

2. Calculate MTD (for the previous month):

Sum({<Month = {"$(=Month(AddMonths(Max(Month), -1)))"}, Year = {"$(=Max(Year))"}>} Sales)

This set expression calculates the sum of Sales for the previous month. We use `Month` and `Year` fields to filter for the previous month relative to the maximum selected Month.

3. Calculate YTD (Year-to-Date):

Sum({<Year = {"$(=Max(Year))"}>} Sales)
This set expression calculates the Year-to-Date sum of Sales for the selected Year.

Now, you can use these set expressions in your table to display FTD, MTD, and YTD for Sum(Sales) as separate columns:

LOAD
Date(FKDAT) AS FKDAT,
Floor(Date(FKDAT)) AS DateNum,
Year(FKDAT) AS Year,
'Q' & Ceil(Month(FKDAT) / 3) AS Quarter,
Month(FKDAT) As Month,
Day(FKDAT) As Day,
Week(FKDAT) As Week,
...
Sum(Sales) AS TotalSales
RESIDENT SAP_Data;

In your table object, you can add three expressions as columns:

- Expression 1: FTD
- Label: FTD
- Expression: `Sum({<DateNum = {"$(=Date(DateNum-1))"}>} Sales)`

- Expression 2: MTD
- Label: MTD
- Expression: `Sum({<Month = {"$(=Month(AddMonths(Max(Month), -1)))"}, Year = {"$(=Max(Year))"}>} Sales)`

- Expression 3: YTD
- Label: YTD
- Expression: `Sum({<Year = {"$(=Max(Year))"}>} Sales)`

This should display FTD, MTD, and YTD for Sum(Sales) in your table, with the appropriate values based on your date selections.

By:  Insurance Khabar