Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zahidrahim_ocp
Partner - Creator
Partner - Creator

One Year Filter for multiple analysis

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

10 Replies
ali_hijazi
Partner - Master II
Partner - Master II

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)

I can walk on water when it freezes
daveamz
Partner - Creator III
Partner - Creator III

Hi Zahid,

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

Regards,

David

ogster1974
Partner - Master II
Partner - Master II

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
Partner - Creator
Partner - Creator
Author

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
Creator
Creator

Hi Zahid,

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

Add Qvf.

zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

How to add this master table calendar?

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

ogster1974
Partner - Master II
Partner - Master II

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
Creator III
Creator III

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
Creator
Creator

You can use master calendare below.

Could you share your qvf file?