topic Re: Qlik YTD MTD and Year Filter in App Development

Qlik YTD MTD and Year Filter

NAUS123 — Tue, 02 Jul 2024 22:28:06 GMT

Hi,

I have a filter drop down (YTD, QTD, MTD, WTD) and Year, Month, Day, Week Filter.

When the user selects either YTD or QTD, or MTD or WTD ( no Year, month, day, week filter) then show the sum of sales.
But when they select YTD and Year filter = 2023, then show sum of sales for Jan 1, 2023 - Dec 31, 2023.
Likewise it can be YTD and Year= 2023 and Month = 3 then show sum of sales for Jan 1, 2023 - march 31, 2023.

Re: Qlik YTD MTD and Year Filter

TcnCunha_M — Wed, 03 Jul 2024 13:29:43 GMT

you have to use variable

for example:

vYTD = Year(Max(your_dataField))

vMonth = Month(Max(your_dataField))
vDtIni = Makedate($(vYTD),11);

vDtEnd = MonthEnd(Makedate($(vYTD),$(vMonth) , 1) );

your expression : Sum({< Date_Field = {">=$(vDtIni)<=$(vDtEnd )"} >} Your_measureField)

Re: Qlik YTD MTD and Year Filter

NAUS123 — Wed, 03 Jul 2024 13:53:21 GMT

Thanks for the solution.My fiscal year is not from Jan - Dec. It is from April - march.
I do have Fiscal Year. Fiscal Month, Fiscal Quarter, Fiscal Date fields.

Question -

if I select only YTD, then

vYTD = Year(Max(your_dataField)) . This will be 2024

Month = Month(Max(your_dataField)). This will be 07 (July) .

vDtIni = Makedate($(vYTD),11); , This will be 11/01/2024 . ( Nov -1 , 2024 ) .
Why 11 ?

vDtEnd = MonthEnd(Makedate($(vYTD),$(vMonth) , 1) );

I want end date as of yesterday , not till end of month.

Re: Qlik YTD MTD and Year Filter

TcnCunha_M — Wed, 03 Jul 2024 14:00:03 GMT

vDtIni = Makedate($(vYTD),1,1); ( forgot one coma, to be 1/1/Your_year)

if your fiscal year start on march you can force to be March not January

i force to be end of month they selected. if want to force to be YTD restrict to yesterday date

vDtEnd = Date(Today()-1);

Re: Qlik YTD MTD and Year Filter

NAUS123 — Wed, 03 Jul 2024 14:15:16 GMT

if your fiscal year start on march you can force to be March not January

Yes I can do that in my case : vDtIni = Makedate($(vYTD),1,3);

But when i select now Year filter = 2023 ( with YTD selection intact) , Month = May , then I need YTD dates of course to be May 1 - may 31 to be evaluated in set analysis.

In that case this variable vDtIni = Makedate($(vYTD),1,3); will always default to mar 1, 2023. How to solve this ?