Using parameters in calculated fields

Question & Answer
,
1

Hi everyone,

I am measuring downtime in custome sites. I have created a calculated field that measure the percentage of downtime out of all ground stations as depicted in the following formula:
(sum({Downtime (in Days)})/30.44) / (distinct_count({id[percepto_groundstation]}) * 30.44).

I have also created parameters that allow me to segment data per custome.

However, the percentage chart shows after choosing a specific custome it shows its percentage out of the total and not the it’s downtime out of it’s ground stations.
What I though to do is put a $ before the site parameter to “lock” it. Like I did in another visual where I “locked” the calculation on a date - {Total Downtime} / (dateDiff(${FromDateParamater}, ${ToDateParamater}, “DD”)*distinct_count({ground_station_id}))

My problem is that quicksight does not “find” that parameter when I try to incorporate it in…It finds only the date ones:

image
image745×414 3.2 KB

Any Ideas why? can I only use date parameters in calculated fields?

2

Hi @Etai,
Just out of curiosity, what did you name the parameter you are trying to add to your calculated field?
Normally, there are no limitations to the type of parameter you can use in calculated fields. Whether it’s a date parameter or not, it should still be available to be utilized if using the same dataset.

3

Hi @Brett ,

SiteNameParameter

image

Maybe it does not recognize because it is a string (although supposed to recognize it, not?)?

Thanks,

Etai

4

Hi @Etai,
When creating the parameter, did you setup a static default value? This may not be populating as it would need something there to rely on. If you’d just like your default to be something like ‘All’, you can setup in the following way:

image
image656×591 37 KB

Let me know if this works for your case or if you have any additional questions!

5

@Brett , Nice…
Thank you very much…

I did all values and this was the result:

image
image773×201 5.24 KB

Appreciate it :slight_smile:

6

@Brett any ideas how to incorporate the parameter in the formula?

this is the original formula which I want to add the parameter so it counts the relative downtime of that specific site

(sum({Downtime (in Days)})/30.44) / (distinct_count({id[percepto_groundstation]}) * 30.44)

All formulas I create (usually with an ifelse) result in 0…

Thanks

7

You could try something like this:
sum(ifelse(${siteSITE} = {Site}, {Downtime (in Days)}, 0)/30.44) / (distinct_count(ifelse(${siteSITE} = {Site}, {id[percepto_groundstation]}, NULL) * 30.44)

Additionally though, what’s stopping you from filtering based on the parameter selection?

8

Thanks,
The reason I wanted the formula from the first place was that the parameter and the control presented only the percentage of that specific account out of the overall and did not address it as a whole.
I’ll explain. If I had 125 days of downtime a month with 10 ground stations it is 41% of downtime (each base has 30.44 working days). That works fine. But there are cases where out of the 125 days, 25 of them were in one base station. Thus, when referring only to it was actually 82% downtime.
When I chose a specific account in the control it does not make that adaptation…
Thanks for you effort :slight_smile: