Add Distinct count of rows to a pivot

[Filo]

I have the following data set:

I need to create a pivot that looks like that:

In QS I can’t add a distinct count of responses (so, how many responses were there per each Store - or row, if drilling down). I tried calculating distinct count of responses, but it is only accepted in Values shelf:

Can this case be solved?

Dashboard is here, but I couldn’t share it? (clicking on Permissions does nothing)

Bar colouring for most recent + condition

Hello Team! With the following data: |Date|Value| |2021-04-01|1| |2021-05-01|9| |2021-06-01|9| |2021-07-01|3| I was asked to do the following: find the most recent period for this period check if value is smaller than 9 value smaller than nine is green, otherwise red Present in a form of a bar chart I calculated it in the following way: Find last Date (Created artificial aggregate, IDK if needed?): max(Date, [{Artificial aggregate}]) Check if date is last date and apply colours …

[Nico]

Hi @Filo ,

do I get you right? You want a pivot table in the following form:

First I tried the following:
Main issue is, that D count Repsonse ID must be stored in the rows. But this is not possible for measure values. Converting to a dimension is possible by using the “toString” function, but then we have a custom aggregation. Custom aggregations are permitted to add to rows.

Only solution I found is to create two tables, the first shows your D count Response Id field, the second shows your pivot with the measures. Not the solution I wanted to present but one that works.

Best regards,
Nico

[Nico]

Hi @Filo,

This answer is separated from my first one, because it concerns another topic.

I did not understand your topic with sharing the dashboard.

Did you follow this instruction?

QuickSight Arena -- An embedded instance of QuickSight within the QuickSight Community

[FeatureTile 11(2)] QuickSight Arena provides you with an embedded instance of QuickSight right within the QuickSight Community. Using Arena, you can: Explore creating QuickSight data visualization dashboards for free Publish dashboards to the Community Gallery to showcase your skills Include dashboards depicting your use case while posting questions in the Community Q&A Clone underlying analysis views of published dashboards in order to learn / try answering questions Include dashboards, w…

Best regards,
Nico

[Filo]

Thanks for responding!

That’s right, I see no way to have this in one table in QS - sad!

And the dashboard is here → KPI for aggregated values - #3 by Filo

But I couldn’t share it, clicking on share → permissions does nothing?

[Nico]

Hi @Filo ,

regarding sharing your analysis.

Before you click on share, the analysis needs to be published as a dasbhoard.
Therefore click on “Publish” in the right upper corner.

After that you click on the “share” icon on the left side:

Now you can switch from OFF to ON. (You need to confirm the Grant public access dialog box.
After that you can click on “Copy link” to receive your shareable link.

Best Regards
Nico

[Xclipse]

Hi @Filo, I am marking this reply as, “Solution,” in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)