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]
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
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])
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
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
hi sunindia,
None of both expression works
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.
What about this?
Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'},[Fiscal_Week]={'<=40'}>}[NETSALES])
Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'},[Fiscal_Week]={'<=$(=Today())'}>}[NETSALES])
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
Sorry forgot to add Week function:
Sum({<[Fiscal_Year]={'$(=Year(Today())-1)'},[Fiscal_Week]={'<=$(=Week(Today()))'}>}[NETSALES])
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