Skip to main content
Solved

Count number of occurrences and mark the last occurrence

  • March 6, 2024
  • 5 replies
  • 367 views
W
Master Trendsetter
Forum|alt.badge.img+8

Hello,

I currently have the following table, in the first column I fill in tickets manually.
And now I have two needs:

  1. I hope to count the number of corresponding Tickets through the second column.
  2. I hope to mark the last ticket in sequence.

I tried using the Rank function, but couldn't achieve these two needs accurately. Can you give me some suggestions?

Thank you in advance!!

Best answer by Issam Moalla

HI @Weining Ben ,

A possible solution would be using the BY → Modifier to create a table adding the Tickets as a dimension which would be used in your formula:
 

  • For the count metric I would apply the following formula: 
    IFDEFINED('Ticket Request'[BY: -> 'Ticket Request']
      ,Cumulate(IF(ISDEFINED('Ticket Request'[BY: -> 'Ticket Request']),1),ID100))
      [REMOVE: Tickets]

    where:

    • IF(ISDEFINED('Ticket Request'[BY: -> 'Ticket Request']),1) would attribute 1 when a ticket is found

    • Cumulate(IF(ISDEFINED('Ticket Request'[BY: -> 'Ticket Request']),1),ID100)) would cumulate the count for the tickets

    • IFDEFINED('Ticket Request'[BY: -> 'Ticket Request'],,,) would restrict the cumulate only where the cells are defined

    • Finally the [REMOVE: Tickets]  would remoove the ticket dimension and keep the count on the corresponding line

  • For the Last check the formula would be:

    IFDEFINED('Ticket Request'[BY: -> 'Ticket Request'],ID100)[REMOVE LASTNONBLANK: ID100][BY: Tickets -> 'Ticket Request'] = ID100

    where:

    • IFDEFINED('Ticket Request'[BY: -> 'Ticket Request'],ID100) would attribute the line ID where a ticket is found

    • [REMOVE LASTNONBLANK: ID100] would keep the lastnonblank line ID value to each ticket

    • [BY: Tickets -> 'Ticket Request'] would remove the ticket dimension and mapping to the corresponding entry line based on the ticket request metric


Hope this answers your question,
Issam

Amal B
Rebe

5 replies

Issam Moalla
Employee
Forum|alt.badge.img+11
  • Issam MoallaEmployee
  • Employee
  • Answer
  • March 6, 2024

HI @Weining Ben ,

A possible solution would be using the BY → Modifier to create a table adding the Tickets as a dimension which would be used in your formula:
 

  • For the count metric I would apply the following formula: 
    IFDEFINED('Ticket Request'[BY: -> 'Ticket Request']
      ,Cumulate(IF(ISDEFINED('Ticket Request'[BY: -> 'Ticket Request']),1),ID100))
      [REMOVE: Tickets]

    where:

    • IF(ISDEFINED('Ticket Request'[BY: -> 'Ticket Request']),1) would attribute 1 when a ticket is found

    • Cumulate(IF(ISDEFINED('Ticket Request'[BY: -> 'Ticket Request']),1),ID100)) would cumulate the count for the tickets

    • IFDEFINED('Ticket Request'[BY: -> 'Ticket Request'],,,) would restrict the cumulate only where the cells are defined

    • Finally the [REMOVE: Tickets]  would remoove the ticket dimension and keep the count on the corresponding line

  • For the Last check the formula would be:

    IFDEFINED('Ticket Request'[BY: -> 'Ticket Request'],ID100)[REMOVE LASTNONBLANK: ID100][BY: Tickets -> 'Ticket Request'] = ID100

    where:

    • IFDEFINED('Ticket Request'[BY: -> 'Ticket Request'],ID100) would attribute the line ID where a ticket is found

    • [REMOVE LASTNONBLANK: ID100] would keep the lastnonblank line ID value to each ticket

    • [BY: Tickets -> 'Ticket Request'] would remove the ticket dimension and mapping to the corresponding entry line based on the ticket request metric


Hope this answers your question,
Issam

W
BrunoRodriguez
W
Master Trendsetter
Forum|alt.badge.img+8
  • Weining BenMaster Trendsetter
  • Author
  • Master Trendsetter
  • March 7, 2024

Hello @Issam Moalla ,

Thanks a lot for your solution!

  1. It nearly achieved my need, and I adjusted it based on your formula, now it’s 
IFDEFINED('Ticket Request'[BY: -> 'Ticket Request']
  ,Cumulate(IF(ISDEFINED('Ticket Request'[BY: -> 'Ticket Request']),1),'Test Items'))[REMOVE MAX: ID100][BY: Ticket -> 'Ticket Request']
  1. It’s exactly what I want! 

 

Thanks again for your help!

Weining

Benoit
Rebe
Benji
francois
Employee
Forum|alt.badge.img+13
  • francoisEmployee
  • Employee
  • March 7, 2024

Hi!
I have not looked into the Last item part, but you can also use RANK to compute the number of items, in a much more compact way:

RANK('List of Tickets'.Ticket.Name, 'List of Tickets'.Ticket, asc, maximum)

Hope this helps!

François

François (Pigment)
Rebe
Issam Moalla
W
W
Master Trendsetter
Forum|alt.badge.img+8
  • Weining BenMaster Trendsetter
  • Author
  • Master Trendsetter
  • March 7, 2024

Hello @francois 

 

It really help me out!

And I just noticed that your formula is also useful even the Ticket column is in format Text. It’s helpful for me.

 

Thanks for you help!

Rebe
Issam Moalla
A
Author
Forum|alt.badge.img+5
  • A_GarneriAuthor
  • Author
  • October 22, 2024

Hello @Weining Ben

I know I might be a bit late to jump in, but I have another solution for this



First, you must set up an Unique ID Property in your list:
 


Now, for your total “Count” and “Last Check” you have make a formula like this:

Count:
'Mark Last Ocurrence'.'Ticket request'[BY COUNT:'Mark Last Ocurrence'.'Ticket request' ][BY CONSTANT: Tickets->'Mark Last Ocurrence'.'Ticket request']

Last Check: 
'Mark Last Ocurrence'.'Count'= RANK('Mark Last Ocurrence'.'Unique ID','Mark Last Ocurrence'.'Ticket request',ASC,SEQUENTIAL)


Now, if anybody it’s trying to replicate an “ISFIRSTOCURRENCE” behavior, you only have to edit “Last Check” formula like this:
 

 

(RANK('Mark Last Ocurrence'.'Unique ID','Mark Last Ocurrence'.'Ticket request',ASC,SEQUENTIAL)=1) [FILTER: ISNOTBLANK('Mark Last Ocurrence'.'Ticket request')]

Hope this helps
Regards,

Alberto Garneri.

Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement