topic Re: YTD for fiscal year in QlikView

YTD for fiscal year

Anonymous — Thu, 05 Jul 2018 07:07:48 GMT

Hello everyone,

I stuck on YTD calculation for fiscal year.

I have year from 2002 to 2019 and need to calculate YTD on fiscal year.

for current year(2018) its calculate from Month APR but for rest of the it calculate from Month JAN.

2018 JAN 10

2018 FEB 15

2018 MAR 30

2018 APR 5

2018 MAY 10

2018 JUN 25

WHEN I SELECT YEAR 18 AND MONTH APR THEN ITS SHOWS 5 & I F SELECT MAY THEN IT SHOWS 15

2017 JAN 20

2017 FEB 11

2017 MAR 20

2017 APR 15

2017 MAY 6

2017 JUN 5

WHEN I SELECT YEAR 17 AND MONTH APR THEN ITS SHOWS 66 & I F SELECT MAY THEN IT SHOWS 72 (It calculate from jan but i need to show from apr)

please help me to find out.

Thanks..

Re: YTD for fiscal year

olivierrobin — Thu, 05 Jul 2018 07:34:17 GMT

hello

which formula do you use to calculate your sum ?

do you use a calendar ?

Re: YTD for fiscal year

Anonymous — Thu, 05 Jul 2018 07:46:31 GMT

=SUM({<Region={'INDIA'}, FiscalYear={{$(=FYChart3)},Month={"<=$(=vMonthNo)"}>}Value)/1000

vMonth3.1 =If(Isnull(getfieldselection(Month,"",""), Max(month),(getfieldselection(Month,"","")

FYChart3= If(Isnull(getfieldselection(FiscalYear,"",""), Max(FiscalYear),(getfieldselection(FiscalYear,"","")

vMonthNo =IF(vMonth3.1 ='Jan',1,IF(vMonth3.1 ='Feb',2,IF(vMonth3.1 ='Nov',11,'12')))))) .........

Re: YTD for fiscal year

Anonymous — Thu, 05 Jul 2018 07:49:05 GMT

For selected year

=SUM({<Region={'INDIA'}, FiscalYear={{$(=FYChart3)},Month={"<=$(=vMonthNo)"}>}Value)/1000

vMonth3.1 =If(Isnull(getfieldselection(Month,"",""), Max(month),(getfieldselection(Month,"","")

FYChart3= If(Isnull(getfieldselection(FiscalYear,"",""), Max(FiscalYear),(getfieldselection(FiscalYear,"","")

vMonthNo =IF(vMonth3.1 ='Jan',1,IF(vMonth3.1 ='Feb',2,IF(vMonth3.1 ='Nov',11,'12')))))) .........

and for last year

=SUM({<Region={'INDIA'}, FiscalYear={{$(=FYChart3-1)},Month={"<=$(=vMonthNo)"}>}Value)/1000

Re: YTD for fiscal year

agastya — Thu, 05 Jul 2018 07:51:54 GMT

Are you talking about MTD or YTD?

Re: YTD for fiscal year

Anonymous — Thu, 05 Jul 2018 08:07:57 GMT

YTD

Re: YTD for fiscal year

Anonymous — Thu, 05 Jul 2018 12:47:11 GMT

Hi Prachi,

Can you share sample app and expected result?

Thanks!!

Re: YTD for fiscal year

Anonymous — Fri, 06 Jul 2018 05:34:11 GMT

Please check and let me know.i can't share sample app.

Re: YTD for fiscal year

Anonymous — Fri, 06 Jul 2018 07:25:00 GMT

Hi Prachi,

PFA.

Re: YTD for fiscal year

MK9885 — Fri, 06 Jul 2018 16:54:35 GMT

Your data in word document is a big pain to work on....

XL files is always easier....

anyway, if you have a Num Month field, as in Month numbers then create a flag in back end like below...

if(Num_Month>=4,1,0) as FYTD_Flag, <<< This may be a preceding load depending on how the data is loaded.

After having that flag in front end

You may need 4 different expressions

1. Sum({ < Year={2018} >} Production) >> Selected Month

2. Sum({ < Year={2017} >} Production) >> Selected Month

3. Sum({ < Month = {"<=$(=num(Month))"} , FYTD_Flag={1},Year={2018} >} Production) >>2018 YTD

3. Sum({ < Month = {"<=$(=num(Month))"} , FYTD_Flag={1},Year={2017} >} Production) >>2017 YTD

Or provide a clean data in XL file to work on?

I tested it on my end and adding a flag will only start the Month from Apr, this will be static

Re: YTD for fiscal year

Anonymous — Sat, 07 Jul 2018 04:13:06 GMT

can you please share a screenshot. i am not able to view this file.

Re: YTD for fiscal year

PunamWagh — Sat, 07 Jul 2018 07:17:37 GMT

Try this,

Backend --

Load *,

Date#(Capitalize(Month)&' '&Year,'MMM YYYY') as Year_Month;

Load * Inline [

Year , Month , Count

2018,JAN,10

2018,FEB,15

2018,MAR,30

2018,APR,5

2018,MAY,10

2018,JUN,25

2017,JAN,20

2017,FEB,11

2017,MAR,20

2017,APR,15

2017,MAY,6

2017,JUN,5 ];

SET vFiscalYearStartMonth = 4;

LET vStartDate = 42461;

LET vEndDate = 43282;

Let vToday1 = num(Today()-1);

CYCalendar:

Load

Month(Test_date) AS Month_Cal,

YearName(Test_date, 0, $(vFiscalYearStartMonth)) AS FiscalYear,

Num(Test_date) as C_ACCOUNTING_DATE_NUM,

date(Test_date,'MMM YYYY') as Year_Month

;

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Test_date ,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

in frontend-

use this expre-

Sum({<Test_date = {">=$(=Yearstart(max(Test_date),0,4))<=$(=max(Test_date))"},Month=>}Count)

Take filters - FiscalYear & Month

Re: YTD for fiscal year

arvind_patil — Sat, 07 Jul 2018 08:33:43 GMT

Hi Prachi,

for your Current year YTD write formula as below:

Sum({<Date={">=$(=YearStart(Max(DateFiled),0,4))<=$(=Max(DateFiled))"}>} Value)

Thanks,

Arvind Patil