Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Experts,
I have a dash board with Sales and Stock Data. Now both analysis have date fields in their own different tables. I want to apply a single year filter which can filter both analysis sales as well as stock.
What should be the date i select as year filter?
Regards,
Zahid Rahim
create a variable let's say vYear
in the definition put =max(Year)
then use this value in your set analysis like
sum({<StockYear={$(vYear)}>}stock_qty)
Hi Zahid,
Have a look at this blog post: https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date
Regards,
David
Create a seperate master calendar which you can then link your two dates to via set analysis. something like
Create a filter on your sheet with your master date values.
Then join your sales and stock to it using set analysis.
Sum({<$ date1=p(mdate)>} Sales)
Sum({<$ date2=p(mdate)>} Sales)
Dear Ali,
Thank you for the reply but i want to give user a filter i have created a variable but how to create a filter based on this variable?
Regards,
Zahid Rahim
Hi Zahid,
You can create master table - Calendar table. Stock and Sales tables connect to the calendar table.
Add Qvf.
How to add this master table calendar?
And do i need to perform the same for month and day?
If you are setting the option in a variable you just need the set analysis part.
Sum({<date2=$(vdate)>} Sales)
Hi Rahim,
Create a master calendar based on minimum date of your data. Have a look at below script..
**********************************************************************************************************************************
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min([Date Entered]) as minDate,
Today(1) as maxDate
Resident YourDataTable;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS [Date Entered],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar Order By TempDate ASC;
Drop Table TempCalendar;
You can use d.prashanthreddy master calendare below.
Could you share your qvf file?