Hi, similar to the Demo insight "Campaign Spend Quarterly Benchmarking (All Time)" I would like to create a Stacked Area Chart on a Metric "Amount Spend Last Year" and view it by Month. I created the metric "Amount Spend Last Year" as follows.

SELECT SUM(Item Grossamount) WHERE Year (Item Settlementdate) = THIS -1 WITH PF EXCEPT Date (Item Settlementdate)

I would like the chart to show the complete Last Year for each month even if the date (Item Settlement date is manipulated through filters). I thought it could be done through the "WITH PF EXCEPT" clause. But so far by manipulating the Item Settlement date in the filter my chart is still impacted. Is there something else that prevents the date from impacting my chart? 🤔

Hello Hans, If you use

WITH PF EXCEPT Date (Item Settlementdate)

the behaviour should be as intended - the date filter in insight won’t affect the outcome and you will see only values for

year=THIS

which is correctly displayed on your screenshot. However, if you place the insight to a dashboard, the dashboard’s date filter will still somehow affect the chart, depending on the selection. But in the insights configuration on the dashboard, you can choose which filters will apply to the inisght, so you can exclude the date filter. Is this what you are after?

I am afraid the WIITH PF EXCEPT does not work as you expect. It probably cannot block the filter to apply if the insight is sliced by Month/Year attribute. Maybe you can use simple metric:

SELECT SUM(Item Grossamount) WHERE Year (Item Settlementdate) = THIS -1

and do not specify date filter in insight. Add the insight to dashboard and in the cofiguration of filters uncheck Date filter. The dashboard filter will be ignored and filter in the metric will be applied.

Hi Boris and Jacub, indeed the graph does the trick by unchecking the Date filter off the Insight on the dashboard. Thanks for that.

The dashboard date filter overrides the insight date filter if they both use the same date dataset (dimension) (if enabled of course in the widget configuration on dashboard). Both filters are applied if you select different date dataset in the configuration on dashboard than in the filter in insight. Please note that this is different from attribute filters for which both filters are applied (the attribute filter on dashboard as well as filter in insight). The granularity of date filter and granularity of the results given by View by are independent. Some combinations however make little sense. For example filter for last 7 days combined with trend viewed by year will produce result which may seem to show total for 2022, but the data under 2022 category will be only for last 7 days instead. I hope it answers your question.

I understand it now. Thanks for the clarification. 🙏