QlikSense MTD,YTD,QTD

[kmmqlick]

In my sheet i have 3 filters Year, MonthYear(ex-:May-2020) and a drildown filter (Dialy,MTD,YTD,QTD) .

Dimensions are Customer , salesRep, Year, MonthYear, Date

Measure-Sales

I am taking a pivot table with Customer(Row left) and SalesRep(Col Top) i am calculating %Gain(Ratio of CY Sales,PY Sales-Based on selection)

when I select MonthYear ---ex-'May-2020' i want Ratio of  May-2020 sales to May-2019 sales. Please suggest expression for this.

[GaryGiles · Accepted solution]

You have a lot going on here.  You can start by adding separate SalesYear and SalesMonth fields to your table in the load script to make it easier to use set analysis.  Then you could use the following expression in your pivot.  You will need to make adjustments based on what you want the pivot to present if no YearMonth is filtered or if multiple YearMonths are selected and how the Daily, MTD, YTD and QTD filters apply. 

=(sum({$<MonthYear=,SalesYear={$(=max({$} Year(Date)))},SalesMonth={$(=Concat(Distinct SalesMonth,','))}>} Sales) -

  sum({$<MonthYear=,SalesYear={$(=max({$} Year(Date))-1)},SalesMonth={$(=Concat(Distinct SalesMonth,','))}>} Sales)) /

  sum({$<MonthYear=,SalesYear={$(=max({$} Year(Date))-1)},SalesMonth={$(=Concat(Distinct SalesMonth,','))}>} Sales)