One Year Filter for multiple analysis

[zahidrahim_ocp]

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

[ali_hijazi]

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)

[daveamz]

Hi Zahid,

Have a look at this blog post: https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Regards,

David

[ogster1974]

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)

[zahidrahim_ocp]

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

[gladi-cz]

Hi Zahid,

You can create master table - Calendar table. Stock and Sales tables connect to the calendar table.

Add Qvf.

[zahidrahim_ocp]

How to add this master table calendar?

And do i need to perform the same for month and day?

[ogster1974]

If you are setting the option in a variable you just need the set analysis part.

1. Sum({<date1=$(vdate)>} Sales)

Sum({<date2=$(vdate)>} Sales)

[d_prashanthredd]

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; 

[gladi-cz]

You can use d.prashanthreddy master calendare below.

Could you share your qvf file?