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]

Period:

LOAD

    PeriodId,

    Year,

    Month,

    Day,

    Week_Day,

    Week_Number,

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

    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]

Try this:

=Sum({<Date={"$(='>=' & Date(YearStart(AddYears(Max(Date),-1)), 'MM/DD/YYYY') & '<=' & Date(AddYears(Max(Date),-1), 'MM/DD/YYYY'))"}>}[NETSALES])

[#3]

Hi sunindia,

Tried, but doesn't work.

I was thinking if there is a way that after having the Fiscal Year be 2014, then Fiscal week be <= current fiscal week I am right now.

It is like, SUM Netsales from Fiscal Week less than 40 (current fiscal week) of Fiscal Year 2014.

Like this example below, if I remove the highlighted part it shows me daily netsales of all fiscal year -1, but it shows me all 12 month, and what we need is to show all day until the current fiscal week.

I can use the filter to do that, but is more like for users can see it without the need of the filter.

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

btw, I am new to Qlik product.

Thanks

[sunny_talwar]

When you put this: ='>=' & Date(YearStart(AddYears(Max(Date),-1)), 'MM/DD/YYYY') & '<=' &Date(AddYears(Max(Date),-1), 'MM/DD/YYYY') in a text box object -> Do you get the right date range and in same format as your date?

Also, I don't understand, does you expression works or not (with Fiscal_Week in it)?

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

Best,

Sunny

[#5]

hi sunindia,

None of both expression works

[#6]

If I do this:

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

If will show me only the days of Fiscal Week 40 from Fiscal year 2014, but doesn't show me fiscal week 1 to 39. and want this to be kind of automatic, like if I am in Fiscal Week 41, then it should be 41, because once published, I can't modify this every week.

[sunny_talwar]

What about this?

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

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

[#8]

What about this?

Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'},[Fiscal_Week]={'<=40'}>}[NETSALES])   <<<< This Works it shows Fiscal Week 1 to Fiscal Week 40 for FY2014

Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'},[Fiscal_Week]={'<=$(=Today())'}>}[NETSALES])   <<<< This shows Fiscal Week 1 to 52 for FY2014, still doesn't get today Fiscal Week number

[sunny_talwar]

Sorry forgot to add Week function:

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

[#10]

Today Week is Week 27 according to calendar, so it brought up till week 27, but fiscal week 27 is up to April 5th.

=S