Use the result of countdistinct to multiply for a number

Jesus Villegas shared this question 8 months ago
Answered

I'm using the countdistinct to get the total in days for a specific period of time (using the field submit date)

For example I get the count value for the last 5 days and it returns 5 as expected

but after that when I try to use this same value multiplied for 2 or any other number I get a very different value.

I need to be able to multiply the value I get from the count days of the period * 1440 (minutes per day)

Help please!

Jesus

Comments (1)

photo
1

Hi Jesus,

Thanks for reaching out.

I think what's likely happening here is that if you're just doing [field] * 2, but you want a distinct count of said field * 2, then [field] * 2 wouldn't be correct. Instead you'd need to do COUNTDISTINCT[field] * 2.

To use an example with our tutorial data:

/8BllORaVDUOhUAAAAASUVORK5CYII=

You can see the value is correct. If I instead do '(Camp ID) * 2', the result is quite different:

/A3xoBBwAApA4BBwAApA4BBwAApA4BBwAApA4BBwAApA4BBwAApA4BBwAApA4BBwAApA4BBwAApM7AgPP+jz+5uLi4buzib82ncwFp9P8BHHWZCwx9uTkAAAAASUVORK5CYII=

This isn't wrong though, per se. It's just that it's doubling each result set in the data, which an be proven by un-aggregating the Camp ID values:

/A4KbG32RBuLjAAAAAElFTkSuQmCC

This is why this would all be dependent on your setup.

If this doesn't get you to where you're going, can you please send screenshots of your report setup so I can better understand what's trying to be accomplished, as well as a screenshot of the calculation being attempted?

Thanks,

Mike

photo
1

Hello Mike for your help I made this test but didn't work. This is the filter on date so I get 5 days.0769cb7149ba00a4a9a3cd30d5051e42


This is what I understood how you said it should be configured, however as you see I configured it the same way and still shows a big number.


595bd322a3ac1d520fedc3ea4e5fa8f2


This is my query after I modified it as you saidfa0542b0757ab5c791d58d97953d78c5

photo
1

Let me tell you what Im actually looking for and probably you might give me a better way to accomplish this:

I create a report and added a date filter in it so when the report is open the user is able to select a period of days to get information.

With these days that have been selected I need to be able to count them and use this number in my report. Is this possible?


Thank you.

Jesus

photo
1

I got this resolved, I had to use an advanced function to multiply the number, your solution worked for other type of fields, but when I was using it with my date field it didn't work.

photo
1

Hi Jesus,

That's great news! Glad this is sorted.

This considered, I'll go ahead and close this case out now then, but please don't hesitate to reach out with any other questions or concerns.


Regards,

Mike

photo