topic Re: YTD vs LYTD in App Development

YTD vs LYTD

Wed, 01 Jul 2015 14:06:24 GMT

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]

Re: YTD vs LYTD

sunny_talwar — Wed, 01 Jul 2015 16:04:32 GMT

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

Re: YTD vs LYTD

Wed, 01 Jul 2015 16:31:31 GMT

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

sunny_talwar — Wed, 01 Jul 2015 16:37:41 GMT

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

Wed, 01 Jul 2015 16:41:31 GMT

hi sunindia,

None of both expression works

Re: YTD vs LYTD

Wed, 01 Jul 2015 16:50:04 GMT

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

sunny_talwar — Wed, 01 Jul 2015 16:51:26 GMT

What about this?

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

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

Re: YTD vs LYTD

Wed, 01 Jul 2015 17:05:26 GMT

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

Re: YTD vs LYTD

sunny_talwar — Wed, 01 Jul 2015 17:07:05 GMT

Sorry forgot to add Week function:

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

Re: YTD vs LYTD

Wed, 01 Jul 2015 17:13:51 GMT

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

Re: YTD vs LYTD

sunny_talwar — Wed, 01 Jul 2015 17:17:39 GMT

Hahahaha try this:

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

Re: YTD vs LYTD

Wed, 01 Jul 2015 17:28:10 GMT

It shows me the 52 Weeks. =(

Re: YTD vs LYTD

sunny_talwar — Wed, 01 Jul 2015 17:30:48 GMT

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

Wed, 01 Jul 2015 17:34:53 GMT

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

Re: YTD vs LYTD

sunny_talwar — Wed, 01 Jul 2015 17:36:43 GMT

Hahahaha great

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

Re: YTD vs LYTD

Wed, 01 Jul 2015 17:40:27 GMT

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

Re: YTD vs LYTD

sunny_talwar — Wed, 01 Jul 2015 17:43:03 GMT

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

Wed, 01 Jul 2015 17:47:03 GMT

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

Re: YTD vs LYTD

sunny_talwar — Wed, 01 Jul 2015 17:49:19 GMT

Then try this:

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

Re: YTD vs LYTD

Wed, 01 Jul 2015 17:56:40 GMT

no, it didn't work.

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