39 Replies Latest reply: Jul 10, 2015 5:42 AM by Sunny Talwar

# 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:

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:

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]

• ###### Re: YTD vs LYTD

Period:

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:

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:

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

hi sunindia,

None of both expression works

• ###### Re: YTD vs LYTD

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.

• ###### Re: YTD vs LYTD

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

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

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

Sorry forgot to add Week function:

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

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

Hahahaha try this:

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

• ###### Re: YTD vs LYTD

It shows me the 52 Weeks. =(

• ###### Re: YTD vs LYTD

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])

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

Hahahaha great

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

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

Then try this:

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

• ###### Re: YTD vs LYTD

no, it didn't work.

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

• ###### Re: YTD vs LYTD

Actually you NETSALES should work:

Try this:

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

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

What do you get for this in a text box?

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

• ###### Re: YTD vs LYTD

Give me numbers from 1 to 40

• ###### Re: YTD vs LYTD

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?

• ###### Re: YTD vs LYTD

it gives me all year still

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

Thanks sunindia!

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

• ###### Re: YTD vs LYTD

Hahahaha told you its working

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

Best,

Sunny

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

Hey sunindia,

I know this is literally solved.

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

• ###### Re: YTD vs LYTD

Not sure what you mean? Can you elaborate?

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

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])

• ###### Re: YTD vs LYTD

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

hahaha

• ###### Re: YTD vs LYTD

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

• ###### Re: YTD vs LYTD

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