Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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"}, .....>} ....
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)