Replicating Tableau FIXED LOD in Quick Sight – DISTINCTCOUNTOVER_INVALID_ARGUMENT in Pivot Table

Q&A
, ,
1

Hi team,

I am trying to replicate a Tableau FIXED LOD calculation in QuickSight.

Tableau (working):
SUM({
FIXED DATEPART(‘month’,[Sales Period]),
(IF [Sales Period] <= [DateLimit]
THEN DATENAME(‘weekday’,[Sales Period])
END)
: COUNTD(DATETRUNC(‘week’,[Sales Period]))
})

(DateLimit is a Date parameter with default value 2020-09-01 and Sales Period is direct date field)

QuickSight attempt:
distinctCountOver(
truncDate(‘WK’,{Sales Period}),
[
extract(‘MM’,{Sales Period}),
ifelse({Sales Period}<=${DateLimit},
formatDate({Sales Period},‘EEEE’),
NULL)
],
PRE_AGG
)

This throws:
”Please correct the distinctCountOver to use PRE_AGG or PRE_FILTER levels and partiton only by column references.”

So I created calculated fields:

weekstart = truncDate(‘WK’,{Sales Period})
month = extract(‘MM’,{Sales Period})
weekday = ifelse({Sales Period}<=${DateLimit},
formatDate({Sales Period},‘EEEE’),
NULL)

Then used:
distinctCountOver(weekstart, [month, weekday], PRE_AGG)

Now the field validates, but when added to a Pivot Table visual it throws:
DISTINCTCOUNTOVER_INVALID_ARGUMENT

How can I correctly implement this Tableau FIXED logic (Month + Weekday → COUNTD Week) in QuickSight without this error?

(Note : the quicksight calculation works when partitioning only by month.
But when I add weekday to the partition, the Pivot visual throws DISTINCTCOUNTOVER_INVALID_ARGUMENT.)

2

Hi @VISHNUPRIYA7403,

Welcome to the Quick Community! Please let me know if I am misunderstood your logic, but I believe ‘weekday’ calculated field may not be working because it is formatting a new date instead of ‘month’ which is pulling an original date from the dataset. This could be why Quick Suite is having trouble when ‘weekday’ is added to the partition.

To workaround this, I would recommend adding the same ‘weekday’ calculated field on the dataset prep level, as that should create a new column in your dataset and therefore allow you to pull it to the partition with no error.

Let me know if this helps!

3

Hi @WLS-Luis
Thanks a lot for your help! :blush:
You were right — the issue was with the weekday condition, which was returning NULL for all rows. I recreated the weekday logic at the dataset level, and that resolved the Pivot error. Really appreciate your guidance!

1 Like