Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
Hahahaha try this:
Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'}, [Fiscal_Week]={"<=$(=Week(Max({<[Fiscal_Year]={'$(=Year(Today()))'}>}[Fiscal_Week])))"}>}[NETSALES])
It shows me the 52 Weeks. =(
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])
shows Week 52, wait... my bad... forgot to tell you that Fiscal Year 2015 have all 52 weeks in table.
Hahahaha great
Is there a field which only goes until 40th week in 2015 and is empty after that?
No, the Period table have all date with matching fiscal week for all FY2015, there's no other table. =S
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
PeriodID is linked to all, but I can assume this goes until the day that have data for Sales Table.
Then try this:
Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'}, [Fiscal_Week]={"<=$(=Max({<[Fiscal_Year]={'$(=Year(Today()))'}, PeriodID = {'*?'}>} [Fiscal_Week]))"}>}[NETSALES])
no, it didn't work.
how can you tell the Expression to read Today's Fiscal week number?