7 Replies Latest reply on Nov 18, 2018 2:32 PM by Jennifer VonHagel

# Calculations With Dates Outside View Filter

Hello!

I've got a question that I havn't been able to get an answer to or solve myself so here I am.  As the title suggests, I'm trying to do calculations based on a date field that has a filter for the view, but the calculation contains dates outside that filter.

One Dimension - [Transaction Date]           LOD: yyyy-mm-dd hh:mm:ss

One Measure - [Transaction Amount]         LOD: \$xxxx.xx

Here's what I am attempting.

A dual axis dashboard:

One axis is a vertical bar chart of the sum of [Transaction Amount] over the last 14 days.

The second axis is a gantt chart overlay (or cell reference lines if more applicable) of the average sum of transactions for each day of the weak, of the previous quarter.

Calc Fields:

Number of Weekdays:

{FIXED DATENAME('weekday', [Transaction Date]), DATE([Transaction Date]): COUNTD(DATENAME('weekday',[Transaction Date]))}

This finds the number of weekdays broken by current dimension in view, but I need it to be the number of each weekday in the previous quarter, not the current view.

Previous Quarter Bool:

IF DATEPART('quarter', [Transaction Date]) = DATEPART('quarter', TODAY()) - 1

AND DATEDIFF('month', [Transaction Date], TODAY()) <= 6

THEN TRUE

ELSE FALSE

END

I know I need something along the lines of:

{FIXED DATENAME('weekday', [Transaction Date]) : SUM([Transaction Amount]/SUM([Number of Weekdays])}

However, similar to the issue with [Number of Weekdays], I can't get it to work based off last quarter's numbers, just the last 14 days numbers in the view from filter.

Thanks ahead of time,

Brandon