How to count change of accountdetails per month, per employee, diaplayed in a KPI

Hi,

Consider a dataset like the following, containing AccountNo for various EmployeeNo distributed over Month 1 to 4 (could represent Jan-April).

EmployeeNo,BankCode,AccountNo,Month
A01,Bank04,AC000001,01
A01,Bank04,AC000002,02
A01,Bank01,AC000002,03
A01,Bank02,AC000003,04
A02,Bank02,AC000004,01
A02,Bank02,AC000004,02
A02,Bank02,AC000004,03
A02,Bank02,AC000004,04
A03,Bank04,AC000005,01
A03,Bank04,AC000005,02
A03,Bank04,AC000007,03
A03,Bank04,AC000007,04
A04,Bank01,AC000008,01
A04,Bank01,AC000009,02
A04,Bank02,AC000009,03
A04,Bank03,AC000010,04

Given a Month, eg. 2 (feb), we want to count those EmployeeNo who had their AccountNo changed since last month, i.e. since January.

ATTEMPT 1:

Our initial attempt is to extend each record in the dataset with the AccountNo for the previous month here using the LAG function:

AccountNoPrev = lag(AccountNo,[Month ASC],1,[EmployeeNo])

Then, with that calculation, we try to add a second calculation, with the purpose of checking if the AccountNo for THIS Month is changed since last month, using the function locate(), which can check for substring essentially:

AccountNoChanged = ifelse(locate(AccountNo,{AccountNoPrev}) > 0,‘NO’,‘YES’)

However, this calculation cannot be defined, when one of the operands is itself a calculated field.

Another issue is the use of the LAG() function. Belonging to the group of Table Calculations in QuickSight, it cannot be used in a KPI (not even in a filter on a KPI, since its defining operands needs to be contained in the field wells).

This leads us to our second attempt:

ATTEMPT 2:

We enrich our dataset with an extra field, containing for each month, the AccountNo from the previuos Month. It looks like this: (for Month 1, we assume that the AccountNo is the same as the current).

EmployeeNo,BankCode,AccountNo,Month,AccountNoPrev
A01,Bank04,AC000001,01,AC000001
A01,Bank04,AC000002,02,AC000001
A01,Bank01,AC000002,03,AC000002
A01,Bank02,AC000003,04,AC000002
A02,Bank02,AC000004,01,AC000004
A02,Bank02,AC000004,02,AC000004
A02,Bank02,AC000004,03,AC000004
A02,Bank02,AC000004,04,AC000004
A03,Bank04,AC000005,01,AC000005
A03,Bank04,AC000005,02,AC000005
A03,Bank04,AC000007,03,AC000005
A03,Bank04,AC000007,04,AC000007
A04,Bank01,AC000008,01,AC000008
A04,Bank01,AC000009,02,AC000008
A04,Bank02,AC000009,03,AC000009
A04,Bank03,AC000010,04,AC000009

Having the previous AccountNo immediately present in the dataset, we are now able to have a calculation determining if the AccountNo has been changed since last month:

AccountNoChanged = ifelse(locate(AccountNo,AccountNoPrev,1)>0 AND locate(AccountNoPrev,AccountNo)>0,‘NO’,‘YES’)

With this we now define a parameter, and a control, to enable the user to select the current month, in the example below month 2 (feb) is chosen, and Quichsight computes two rows for us, and counts them in a KPI.

Our major question is: Could this have been done differently? More precisely, could we have done it without having to enrich the dataset with an additional column?

Cheers,
Kåre

Hi,

KPI’s only support the one value or some forecast of future and past data etc.
You can use the other Visual to present this data as Multiple transaction cannot show in KPI’s.

Regards,
Naveed Ali

1 Like

Hi @kaare - I have a solution for you, but there is a caveat. The value we are looking for changes on must be a number. I see you have some text characters in your account numbers, so in this potential solution I chose to ignore those and just focus on the numerical parts of the AccountNo. This may or may not be a valid approach with your actual data. Please see this example in Demo Central and edit the Analysis to view the calculated fields used.

https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Flag-Changes-Across-Time-Periods

1 Like

Hi @Jesse - Thank you very much for your proposed solution. It is definetely of interest for us. Computing the “previous” values in the Quicksight analysis, as opposed to having to compute them at the dataset level, using hand-written SQL, is a lot nicer.

Thanks for taking your time, looking into this!

Cheers,

Kåre Kristoffersen
AWS Solutions Architect
KEYCORE, DENMARK

1 Like