I want to execute some advanced math calculations on the results provided from Quicksight analysis. Let’s say for example - a given analysis underneath selected filters has resulted in a 2x2 matrix which I’d like to ingest to a CHI-SQUARED statistical test that will provide a p-value. How can I do that in QuickSight?
You should be able to calculate the chi square statistic value by using aggregate functions and/or table calculation functions. You would use these to create calculated fields that give the result of each cell in your 2x2 matrix.
However, it is harder to get the corresponding p-value because QuickSight does not have a chi-square cumulative probability distribution function which would allow us to get the p-values. You may try doing this by using some approximation function.
You may also consider using QuickSight Url Actions to call an external URL and passing in the data as parameter which then computer the p-value using some standard statistical library (or the even the chi-square statistic itself).
Thank you @darcoli for your answer.
Can you please elaborate regarding QuickSight url action? How can I find an online service that gets those 4 parameters at the url and returns the corresponding p-value?
This is a good getting-started guide for URL Actions URL Actions in AWS QuickSight - YouTube
I do not know if there is an existing service for calculating the chi-square p-value but it should be relatively easy to create one. You can even create entirely using AWS using AWS Lambda and Api gateway and probably you should be able to do it in a few hours.
The following pointers should get you started to create this service:
Creating a simple Hello World service with Lambda and Api gateway Tutorial: Build a Hello World REST API with Lambda proxy integration - Amazon API Gateway
You can then use this Javascript statistics package that provides a Chi-Square Cumulative distribution function
https://github.com/thisancog/statistics.js and documentation here chisquaredcumulativedistribution
You would still need to capture each cell’s value from QuickSight in calculated fields (as mentioned in my previous comment) in order to be able to pass these values in the URL parameters.
Well, so I managed creating a microservice that returns a pval given 4 values with a GET request. Thank you very much for your help.
At this point I just can’t understand how to create four parameters made from four calculated fields (that I already created), so i can cast those parameters within the URL and use custom URL action. In other words, I want to creat four parameters that connect to those four calculated fields identically.
Thank you again!
You need to create four calculated fields that will get the same result being shown for each cell. Without seeing any data or visuals, I would expect this to be doable with calculated fields of the following form:
cell1
sumOver(ifelse(type='A', {value1}, NULL), [], PRE_AGG )
cell2
sumOver(ifelse(type='A', {value2}, NULL), [], PRE_AGG )
cell3
sumOver(ifelse(type='B', {value1}, NULL), [], PRE_AGG )
cell4
sumOver(ifelse(type='B', {value2}, NULL), [], PRE_AGG )
Of course, it would help if you could provide some more insight on the data you are working on and the fields in your table.
Sorry you got me wrong. I already have those four values as calculated fields (of course, no visuals). But now when I try to create the url I can’t find a way to inject those values except using parameters, I.e, I have to create four parameters from those four calc fields. How do I do that?
In other words, now when I have these four calculated fields on my list, how do I create from them four parameters that each one of them is linked to its corresponding cf. didn’t find a way to pass those calc fields to the url directly, so I’ve got no option but create four parameters.
If you have a Table visual, you can add these calculated fields to the visual as well and hide them using the menu shown when clicking the column names in the table header.
Then, it would be possible to choose these fields when creating the URL action:
Thank you again so much. I want to do that otherwise, in addition to some KPI I want to use that microservice to provide the pval besides the KPI.
So below the kpi there’s a custom visual content that uses this service. So right now I’m using some random pre-dertemend numbers, but I want to use those four calculated fields i have to inject them to their corresponding places in the URL.
What should I do in order to make it happen?
I tried to create a parameter that connected to a calculated field, and in set this calculated field to be with the same formula needed for this cell.
The problem is that whenever I use this param in the URL it’s injected as “ALL” string. IDK why.
Here’s a screenshot from the cloudWatch:
You cannot put the url of your service as the url of the Custom Content visual directly because the parameters would not be set when you first load the dashboard. QuickSight does not support populating parameters from the output of visuals or calculated fields directly - but it supports populating parameters after a user clicks on visuals (with Navigation Actions).
So one way to make it work, is to include a table visual that has a link called ‘Update P-value’. When this is clicked, a navigation action would be triggered that sets the parameters from the calculated fields. At this point, your Custom Content Visual should reload and show the right p-value.
Well, now it’s working. Not the best solution but absolutely fine. THANK YOU!
1 Like