YTD vs LYTD

[#1]

Hello Everyone,

I am currently using Qlik Sense, need help on a measurement for YTD and LYTD

At first, I had a measurement like the one below, this shows all FY2015 Sales which is from Oct 1st, 2014 to Sept 30th, 2015, since I have data until today, this works great.

Sum({<[Fiscal_Year]={'$(=Year(Today()))'}>}[NETSALES])

But, If I do the LYTD I just add a -1 to Year,

Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'}>}[NETSALES])

but if I want to specify the exact same amount of week as of YTD, how can I do this?

Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'},[Fiscal_Week]={Today()}>}[NETSALES]) (I know this expression is wrong, just to give you an idea)

Any help Appreciated

Below is the Load Script. That I have

Period:

LOAD

     PeriodId,

     Year,

     Month,

     Day,

     Week_Day,

     Week_Number,

     date(floor(Date),'MM/DD/YYYY'),

     Fiscal_Year,

     'Q'&Fiscal_Quarter,

     Fiscal_Month,

     Fiscal_Week,

FROM [lib://QVDs_Connection/Periods.QVD]

(qvd) Where PeriodId>=20131001;

Sales:

LOAD

    ITEM_ID as "Item ID",

    STORE_ID as "Store ID",

    PERIODID as PeriodId,

    V_ID as "Vendor ID",

    QUANTITY,

    WEIGHT,

    NETSALES,

    DISCOUNT,

    REVENUE,

    DISCOUNT_EMPLOYEE

FROM [lib://QVDs_Connection/SalesDetails_*.QVD]

[sunny_talwar]

Hahahaha try this:

Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'}, [Fiscal_Week]={"<=$(=Week(Max({<[Fiscal_Year]={'$(=Year(Today()))'}>}[Fiscal_Week])))"}>}[NETSALES])

[#3]

It shows me the 52 Weeks. =(

[sunny_talwar]

My Bad again, now this doesn't need Week function. See if this work

Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'}, [Fiscal_Week]={"<=$(=Max({<[Fiscal_Year]={'$(=Year(Today()))'}>} [Fiscal_Week]))"}>}[NETSALES])

[#5]

shows Week 52, wait... my bad... forgot to tell you that Fiscal Year 2015 have all 52 weeks in table.

[sunny_talwar]

Hahahaha great

Is there a field which only goes until 40th week in 2015 and is empty after that?

[#7]

No, the Period table have all date with matching fiscal week for all FY2015, there's no other table. =S

[sunny_talwar]

Something in the fact table like sales, or some kind of measurement, even if it is outside the period table, but is linked to period table on date

[#9]

PeriodID is linked to all, but I can assume this goes until the day that have data for Sales Table.

[sunny_talwar]

Then try this:

Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'}, [Fiscal_Week]={"<=$(=Max({<[Fiscal_Year]={'$(=Year(Today()))'}, PeriodID = {'*?'}>} [Fiscal_Week]))"}>}[NETSALES])

[#11]

no, it didn't work.

how can you tell the Expression to read Today's Fiscal week number?