topic Re: YTD in QlikView

YTD

lionking15 — Wed, 25 Nov 2020 16:16:04 GMT

i wand to create YTD but according to fiscal year that starts from "April" .suppose i am having three fields(book_id,date,customer,cost) and i want to do sum of cost but according to YTD.

EX- if i select date '02/02/2016' then my sum of cost should be from(01/04/2015) to (02/02/2016)

Thanks!!!!!!!!!!

Re: YTD

agomes1971 — Thu, 01 Sep 2016 11:34:39 GMT

Hi,

please see this

YTQ, QTD, MTD and WTD

HTH

André Gomes

Re: YTD

lionking15 — Thu, 01 Sep 2016 11:49:59 GMT

I have seen that but using that query the year starts from 'jan' but in my case it should start from 'april'.

So can you please send me the query for above given condition

Re: YTD

Anonymous — Thu, 01 Sep 2016 11:50:21 GMT

Have a look:

Set Analysis for Rolling Periods

Set Analysis for certain Point in Time

Hope it helps!!

Re: YTD

Anonymous — Thu, 01 Sep 2016 12:01:31 GMT

Try this

Fiscal and Standard Calendar generation

Hope it helps!!

Re: YTD

lionking15 — Thu, 01 Sep 2016 12:09:30 GMT

Hi Anil,

i am having fiscal month and fiscal year as one of my field,can you send me exact set analysis statement!!!

Re: YTD

cesaraccardi — Thu, 01 Sep 2016 12:52:16 GMT

Hi,

Does the fiscal month-year field contain the full date information in the underlying data (day,month and year)? What date format are you using? Depending on how you implement it the set analysis for filtering the dates can be different.

Re: YTD

Anil_Babu_Samineni — Thu, 01 Sep 2016 12:57:51 GMT

You want to show, Fiscal YTD depends on selection. I mean if you select 01st Jan 2016. Here, You want to show From Apr 1st 2015 - Current Selection / What? Can you please elaborate more?

Re: YTD

lionking15 — Thu, 01 Sep 2016 13:00:20 GMT

Re: YTD

lionking15 — Thu, 01 Sep 2016 13:02:42 GMT

if you select 01/01/2016 means summation should be between 01/04/2015 to 01/01/2016

Re: YTD

Anonymous — Thu, 01 Sep 2016 13:02:42 GMT

Hi Giridhar,

The best way to do it is to have Real Date and Fiscal Date. Add to your Real Date -3 months to get Fiscal Date,

LOAD

Date,

Month(Date) as Month,

Year(Date) as Year,

Month(AddMonths(MonthStart(Date),-3)) as FiscalMonth,

Year(AddMonths(MonthStart(Date),-3)) as FiscalYear

...

RESIDENT ...

Then, calculate your YTD with FiscalYear, not with RealYear.

Regards!!!

Re: YTD

Thu, 01 Sep 2016 13:10:54 GMT

Here - Sum({1 <date={">=$(=YearStart(max(date), 0, 4))<=$(=max(date))"}>} cost):

Data:
LOAD
  Floor(Rand() * 100) as book_id
  , Date(Floor(YearStart(Today()) + Rand() * (Today() - YearStart(Today())))) as date
  , Floor(Rand() * 1000) as customer
  , num(Rand() * 100, '####.00') as cost
AutoGenerate 1000;


Calendar:
LOAD
  date
  , Year(date) as dmYear
  , Month(date) as dmMonth
  , Day(date) as dmDay
  , YearStart(date, 0, 4) as FinancialYearStart
;
LOAD
  MinDate + IterNo() - 1 as date
While
  MinDate + IterNo() - 1 <= MaxDate
;
LOAD
  Min(date) as MinDate
  , Max(date) as MaxDate
Resident Data;

And see attachement.

Re: YTD

Anonymous — Thu, 01 Sep 2016 15:38:56 GMT

Hi Nick,

Your calendar:

Calendar:
LOAD
    date
    , Year(date) as dmYear
    , Month(date) as dmMonth
    , Day(date) as dmDay
    , YearStart(date, 0, 4) as FinancialYearStart
    , Year(Addmonths(Monthstart(date),-3)) as FiYear
    , Month(Addmonths(Monthstart(date),-3)) as FiMonth
    , AddMonths(date,-3) as FiDate
    , MonthName(Addmonths(Monthstart(date),-3)) as FiMonthName
;
LOAD
    MinDate + IterNo() - 1 as date
While 
    MinDate + IterNo() - 1 <= MaxDate
;
LOAD
    Min(date) as MinDate
    , Max(date) as MaxDate
Resident Data;

Your YTD Expression based on Fiscal Calendar:

Sum({<dmYear=, dmMonth=, dmDay=, FiYear = {$(=max(FiYear))}, FiMonth = {"<=$(=max({<FiYear={$(=max(FiYear))}>} FiMonth))"}>} cost)

Regards!!!

Re: YTD

Thu, 01 Sep 2016 20:45:52 GMT

Manuel, it was an answer, not a question. Working example was attached with more simple approache.