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 told you its working
I am glad we were finally able to figure it out.
Best,
Sunny
Thanks for your hard work... should have figured out how to send the file first...
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
Hey sunindia,
I know this is literally solved.
Since this shows the current week, how can I do to check on last week?
Not sure what you mean? Can you elaborate?
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
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])
seems it works... But somehow it gives me 0 for week 1...
hahaha
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
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