Fixed distinct count of ID for each week

[Mert]

How can I have a fixed distinct count of IDs for each week so that when click on a data point on a different chart, the count for each week will stay the same? I am able to do it in Tableau using
{ FIXED DATETRUNC(‘week’, [Creation Date]) : COUNTD([ID])}

I am using distinctCountOver({ID},,PRE_AGG) function, but I have 4 filters ( Creation Date, Type, Name, Model name)

[abacon]

Hi @Mert,

Thanks for asking your question and welcome to the QuickSight Community!

I may need further clarification, but if using different visuals, one solution might be to restrict the visuals that the filters are applied to, e.g. In my example below, the Order Date filter only applies to the table visual:

But all filters apply to the line chart visual:

Which filters apply to which visuals can be controlled by editing the filter as follows:

Let me know if this helps.

Many Thanks,
Andrew

[Mert]

Hi Andrew,
Thanks. I actually do want to apply the filter to all visuals in the same sheet as I want to build an interactive dashboard. I have a bar chart for Model Name field. When I click on one model name on the bar chart, I would like the line chart to give me the model’s weekly performance. To do so, I need a fixed weekly total count for that model, so it will always stay the same. Does this make sense?
Thanks,

[abacon]

Hi @Mert,

Thank you, I think I have a better understanding now. I’ve built a new dashboard below, a bar chart with an action filter and a line chart showing 2 lines. The blue line is the weekly distinct count for the selected Product and the red line is the weekly distinct count for all products. The same filters are applied to both visuals, except for the action filter on the bar chart.

Here, there is no product selected in the bar chart:

In this image, the product “ChatBot Plugin” is selected in the bar chart:

To create the “fixed” weekly distinct count for all products I had to create 2 calculated fields:

truncDateWK

truncDate('WK',{Order Date})

and
fixedDCount

distinctCountOver({Customer ID},[truncDateWK],PRE_FILTER)

fixedDCount uses the truncated week calculated field, truncDateWK, as the partition field and the calculation level ‘PRE_FILTER’, so that these values are calculated before the filters are applied.

I hope you can take this and apply it to your data and analysis, let me know if it helps.

Regards,
Andrew

[Mert]

Hi Andrew,
It still does not work. The function “distinctCountOver({Customer ID},[truncDateWK],PRE_FILTER)” gives me the weekly total count before all the filters are applied (6 filters). I want the total weekly count after the filters, but before the Model Name filter is applied. This way, when I select a model name the weekly total won’t change. I tried the “PRE-AGG”. That works, but when I apply the Model Name filter, it does not. How can I exclude the Model Name filter after applying all the other filters?
Thanks,
Mert

[abacon]

Hi @Mert,

One approach to solving this is to use 2 line chart visuals and Free-form mode for the sheet layout.

I’ve split the line chart into 2 line chart visuals which can be overlayed to give the impression that they are one single visual.
The filter action on the bar chart only applies to the lower “Model” visual.

To help explain how this works my examples show the line charts next to each other.

With no filters selected:

With the Product action filter selected:

With only Region filter selected:

With the Product action filter selected and Region filter selected:

With this solution please note the following:

• The Level-aware calculated field is no longer required, just the distinct count
• The y-axis needs to be set to a custom range (in order for the line charts to have the same scale)

Let me know if this is a workable solution, I hope this helps.

Many Thanks,
Andrew

[Mert]

Hi Andrew,
Thank you. I thought there would be a function to work around that. In Tableau, I can use Fixed or Exclude LOD statement, but unfortunately it is not possible in QS.
Thanks,