Count Qty based on the unique identifier

geet.patel
geet.patel
in Modeling

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.

Tagged:

Best Answers

  • jjwikselaar
    jjwikselaar
    Answer ✓

    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:)

     

    Include.jpegRank.jpeg 

     

  • rob_marshall
    rob_marshall
    Answer ✓

    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

     

Answers

  • jjwikselaar
    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.

  • geet.patel
    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.

Categories