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]

Actually you NETSALES should work:

Try this:

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

[#3]

it comes out with all FY2014

There should be a way to have tell him to match today's date and return the week number

[sunny_talwar]

What do you get for this in a text box?

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

[#5]

Give me numbers from 1 to 40

[sunny_talwar]

If it gives you 40 then your problem should be solved:

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

Where =Year(Today())-1 is 2014

and =Max({<[Fiscal_Year]={'$(=Year(Today()))'}, NETSALES = {'*?'}>} [Fiscal_Week]) is 40

So you are getting last years sales only uptil 40th week. Isn't this what you wanted?

[#7]

it gives me all year still

[sasiparupudi1]

Please post sample data

[#9]

Hi Sasidhar,

I've attached all sample data, even I created a QVF so you can see what I am trying to work on.

if you open the qvf file, I have a KPI comparison of YTD vs LYTD, and the LYTD is showing me all the FY2014 instead of only the 40 weeks that we are right now. !

Currently, I am using the fx:

SUM({<[Fiscal_Year]={'$(=Year(Today()))'}[Netsales]) but I want to have this to sum all current fiscal week, that can match, and we can compare apple with apple.

Thanks

[sunny_talwar · Accepted solution]

Seems to be working, just needed to change NETSALES to Netsales

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

Best,

Suny

[#11]

Thanks sunindia!

I don't know what happen yesterday, same fx but didn't work, and now it's working!