Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, is it possible to determine what filter is applied through the expression editor? My use case is this: I have a table showing sales and expense metrics that can be filtered by year, quarter, and month. I am wanting to create a column for the net percent change from previous time period. If it is filtered to year, this will calculate the change in net sales from the current year to the previous. If it is set to quarter, it will calculate the change in net sales from the current quarter to the previous. And lastly, if it is set to months it will calculate the change in net sales from the current month to the previous.
To avoid excessive and redundant columns I am wanting to make the column dynamic where it will perform the calculation based off the current filter set using if statements. Is this possible?
Thanks!
If(GetSelectedCount(monthFilter)>0, netSalesCurrentMonth/netSalesPreviousMonth,
if(GetSelectedCount(quarterFilter)>0, netSalesCurrentQuarter/netSalesPreviousQuarter,
netSalesCurrentYear/netSalesPreviousYear ) )
This assumes year is the default if none are selected, but if that's not the case you can insert a third if() statement (which in your example would result in null if no filter is enabled)
It sounds like you should be able to use GetSelectedCount() on your three fields to achieve this, assuming the filter you are referring to is selecting a specific year / quarter/month.
Thanks for the speedy response. I actually have 3 separate filters. One for year, one for quarter, and one for month. I was hoping to be able to use the name of the filter in an if statement so I know which one is set.
In pseudo code this is what I am trying to do.
if(yearFilter = on, netSalesChange = netSalesCurrentYear/netSalesPreviousYear)
else if(quarterFilter = on, netSalesChange = netSalesCurrentQuarter/netSalesPreviousQuarter)
else if(monthFilter = on, netSalesChange = netSalesCurrentMonth/netSalesPreviousMonth)
If(GetSelectedCount(monthFilter)>0, netSalesCurrentMonth/netSalesPreviousMonth,
if(GetSelectedCount(quarterFilter)>0, netSalesCurrentQuarter/netSalesPreviousQuarter,
netSalesCurrentYear/netSalesPreviousYear ) )
This assumes year is the default if none are selected, but if that's not the case you can insert a third if() statement (which in your example would result in null if no filter is enabled)
You are right, I was not thinking hard enough. Thanks a bunch! I believe this is what I need