Count Qty based on the unique identifier

Hi ,

please go through the attached SS. I am looking for that solution.I have a data module which have a numbered list ref and properties as shown in SS .

I am looking for the number of total email sent by the each unique sender based on the sent status in a new module.

Please help me out with this asap.

Thanks.

Question_37786.JPG

Accepted answers

jjwikselaar

Then maybe the Rank function can help...

In the example below I've created an Include line item that is only TRUE for status 'Sent'

You can then use the RANK function where you use the 'Include' line item to specify which items should be ranked and the 'Sender' to group by.

This will give you the ranking for each Sender. The highest Rank for each Sender is the Sent Quantity.

If you then sort by Rank (highest to lowest) and filter Rank only > 0 then it will give you the information you are looking for I think...

The only problem is that it will also show the lower ranks below. Maybe there is a smart way to filter that as well.... need to think a bit more about it:)

rob_marshall

Geet,

Based on your screenshot, the email address needs to be a list (because you are returning different values for each email address). So, as JJ stated, you will need a differnent module, but based on the email address.

Create the Email Address list (with a Top Level Item called All Email Addresses) based off the first occurrence (function is ISFIRSTOCCURRENCE() ) of the email address from the transaction module.

In the Transaction module, create a line item called Count and hardcode it to 1 (formatted as a number). Create another line item named Sent Count (formatted by the Email Address list) with the formula: if Status="Sent" then email adress else blank.

In the summary module, your list or Applies To should be Email Address with one line item called Summary (formatted as Number). The formula should be Transaction Module.Count[sum:Transaction Module.Sent Count]. Create a second line item, called Valid? (formatted as boolean) with the formula: Summary<>0. Filter on the Valid? line item in a view.

Rob

All comments

jjwikselaar

Hi Geet,

Do you have the Senders setup as a list? If so I think you can do this by adding a line item count to your module with the formula: IF Status = "Sent" THEN 1 ELSE 0

Then you create another module with the dimension Senders and a line item Sender Qty. The Sender Qty line item has the formula: SourcModuleName.Count[SUM: SoureModuleName.Sender]

Attached some screenshots. Hope that helps... another options would be the rank functionality maybe, but this is the easiest I tink.

Source_Module_37815.jpeg

target_Module_37815.jpeg

geet.patel

Hi,

Thanks for this suggestion. I have tried this but the main reason is Sender is not a list based. It's a text property manually uploaded data which can be any value.

So any other work around with this condition?

Thanks,

Geet.

jjwikselaar

Then maybe the Rank function can help...

In the example below I've created an Include line item that is only TRUE for status 'Sent'

You can then use the RANK function where you use the 'Include' line item to specify which items should be ranked and the 'Sender' to group by.

This will give you the ranking for each Sender. The highest Rank for each Sender is the Sent Quantity.

If you then sort by Rank (highest to lowest) and filter Rank only > 0 then it will give you the information you are looking for I think...

The only problem is that it will also show the lower ranks below. Maybe there is a smart way to filter that as well.... need to think a bit more about it:)

rob_marshall

Geet,

Rob

Quick Links

Can you help?

Unanswered questions

Missing Column Headers in Anaplan Export When Using API
Hi Community, I’m encountering an issue where an export created in Anaplan is missing column headers when retrieved via the API. However, when I manually run the same export from the Actions tab, the downloaded file includes the headers. Has anyone experienced this behavior before? I’d appreciate any insights into the root…
Importing from Data Hub: Incorrect or Missing Mappings in Target Model
When importing data from a Data Hub to another model, I'm seeing values in the target model that don't exist in the source. For example, seller assignments are being tagged with subregions that aren't present in the source data - some assignments are missing expected tags, while others are mapped to seemingly random…
ANAPLAN XL View
Hi, I need help in pulling a specific view in Anaplan XL. The first grid should display attributes of various projects, while the second grid should present the monthly financial data for these projects. Thanks in advance.