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]
Actually you NETSALES should work:
Try this:
Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'}, [Fiscal_Week]={"<=$(=Max({<[Fiscal_Year]={'$(=Year(Today()))'}, NETSALES = {'*?'}>} [Fiscal_Week]))"}>}[NETSALES])
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
What do you get for this in a text box?
=Max({<[Fiscal_Year]={'$(=Year(Today()))'}, NETSALES = {'*?'}>} [Fiscal_Week])
Give me numbers from 1 to 40
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?
it gives me all year still
Please post sample data
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
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
Thanks sunindia!
I don't know what happen yesterday, same fx but didn't work, and now it's working!