Calculating items in a field

Question & Answer
,
1

I’m new to QuickSight and this should be an easy answer, I just can’t find how to do it. Below is a count of items in my Operation field. What I want to do is find the difference and percentage difference between STARTs (31353) and TRANSFERs (23334). Eventually, I will plot it by week or month.

image

Thank you

2

Can you try something like this?

countOver((ifelse({operation}='START',{operation},0)),[truncDate('MM',{date})],PRE_AGG)-
countOver((ifelse({operation}='TRANSFER',{operation},0)),[truncDate('MM',{date})],PRE_AGG)

This is grouped to the month. So you might need to play around with partitions / level aware calculations.

1 Like
3

Thanks Max I shortened the calculation to the first half where it does a countOver on jus START. It highlights ifelse({OPERATION}=‘START’,{OPERATION},0) and gives the following error: The calculated field expression countOver(ifelse({OPERATION}='START',{OPERATION},0),[truncDate('MM',{timestamp})],PRE_AGG) is incorrect. Correct the syntax and choose Create again.

Here’s a sample of my dataset:

ID timestamp Country Operation Sub-ID
1 2/1/2023 9:10:27 USA START
2 2/1/2023 9:10:28 USA greeting
3 2/1/2023 9:10:31 USA menu language
4 2/1/2023 9:10:31 USA selection EN
5 2/1/2023 9:10:33 USA menu main
6 2/1/2023 9:10:35 USA selection 1
7 2/1/2023 9:10:38 USA menu sub-1
8 2/1/2023 9:10:42 USA transfer 5558881111
9 2/1/2023 9:10:42 USA END
10 2/1/2023 10:17:27 CAN START
11 2/1/2023 10:17:28 CAN greeting
12 2/1/2023 10:17:31 CAN menu language
13 2/1/2023 10:17:31 CAN selection FR
14 2/1/2023 10:17:33 CAN menu main
15 2/1/2023 10:17:35 CAN abandon
16 2/1/2023 10:17:38 CAN END
17 2/1/2023 10:23:36 USA START
18 2/1/2023 10:23:37 USA greeting
19 2/1/2023 10:23:40 USA menu language
20 2/1/2023 10:23:41 USA selection EN
21 2/1/2023 10:23:42 USA menu main
22 2/1/2023 10:23:46 USA selection 2
23 2/1/2023 10:23:47 USA menu sub-2
24 2/1/2023 10:23:51 USA transfer 5558882222
25 2/1/2023 10:23:52 USA END

In Power BI I would use COUNTX with a filter function.
Thanks

4

Ahh can you try

countOver((ifelse({operation}='START',{operation},NULL)),[truncDate('MM',{date})],PRE_AGG)
1 Like
5

Thanks Max! This should help me start understanding how the formulas work. I’m going back and reading through the document referenced with your first answer. Thanks again.

1 Like