Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

YTD vs LYTD

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]

39 Replies
sunny_talwar

Hahahaha told you its working

I am glad we were finally able to figure it out.

Best,

Sunny

Not applicable
Author

Thanks for your hard work... should have figured out how to send the file first...


sunny_talwar

I have downloaded Qlik Sense recently and the first time I have worked on it, while working on your issue. Had to find my way around it, but was able to locate it somehow

So we both learnt something new today

Not applicable
Author

Hey sunindia,

I know this is literally solved.

Since this shows the current week, how can I do to check on last week?

sunny_talwar

Not sure what you mean? Can you elaborate?

Not applicable
Author

‌this formula is to show me todays fiscal week.

FOr example 41 is this fiscal week number, but if i want to show last week? Like a current fiscal week -1

sunny_talwar

This year (2015)

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

Last Year(2014)

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

Not applicable
Author

‌seems it works... But somehow it gives me 0 for week 1...

hahaha

sunny_talwar

Are you looking for this year or last year or for both this year and last year???

I can test it and may be I can figure what's causing it

sunny_talwar

This is the old file that you shared which had up till 40th week as the max week and the formula I just gave seems to give up till 39th week starting week 1 for both this year and last year.

Have a look.

Best,

Sunny