Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mariamovasheli
New Contributor III

Filtering Dimension As Generic

Hello, 

I am Preparing report about Sales of gas stations and I have 3 Years data and 21 station. But during this 3 years some of this stations was not presented some was purchased or built year ago and  I need to filter only stations that was presented during all those 3 years.

If there is Some formulas you can suggest I would really appreciate.

This is set analysis That I am Using Now. 

Sum({<Date = {"=SetDateYear(Only({1} Date), Year(Today())) <= SetDateYear(Max({1} TOTAL Date), Year(Today()))"},
Year, Month>} FactSoldAmount)

Labels (1)
2 Replies
MVP
MVP

Re: Filtering Dimension As Generic

To put a restriction on Stations that were present in all three years, you could use set analysis something like:

Sum( {<Stations={"=Count(Distinct Year)=3"}, .....>} ....
avkeep01
Valued Contributor

Re: Filtering Dimension As Generic

In your script you can add an extra table like: 

 

StationYears: 
NOCONCATENATE LOAD 
Station, 
COUNT(DISTINCT Year) AS NumberOfYears
RESIDENT YourStationDataTable
GROUP BY Station; 

and then in your set analysis you can use something like: 

Sum({<NumberOfYears={">=3"},Date = {"=SetDateYear(Only({1} Date), Year(Today())) <= SetDateYear(Max({1} TOTAL Date), Year(Today()))"},
Year, Month>} FactSoldAmount)
Community Browser