Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Stuck at wrong count!

Hi All,

I have a requirement like below and I am stuck at the point where I make count for Premium titles because there are titles which belong to Transactional are counted again! Please read the following for details!

I have a bunch of titles which have to be counted into each category.

I have the following dimensions: [Title]; [Provider]; [Display Runtime]

Count the grand total: v_count_GrandTotal = count([Title])

  1. For adult tiltes, filter titles by Adult providers: “Adult01”; “Adult02”; “Adult03”. Count the adult tiltes, then remove them from the the rest of raw data

     --> v_count_Adults = count( If ([Provider] InList ("Adult01";"Adult02";"Adult03"); [Title]); Distinct)

             

          v_TempIsAdults = If ([Provider] InList ("Adult01";"Adult02";"Adult03"); "Adults"; "NA")

          v_IsAdults = Min (If (count([v_TempIsAdults]) In ([Title]) > 1; "Adults"; [v_TempIsAdults]) ForEach ([Title]; [Provider]) )

The two variable v_TempIsAdults and v_IsAdults aim to remove adult titles from the rest of raw data. In other words, titles which are counted in Adults are not counted in the other categories.


Titlev_TempIsAdultsv_IsAdults
AAdutlsAdults
ANAAdults
ANA

Adults

BNANA


   2.  After removing all adult titles, filter the titles which their [Display rumtime] is less than or equal to 10 minutes and their [Provider] is not:                 "ABC"; "DEF". Count the clip titles, then remove them from the rest of raw data

    -->v_count_Clips = count ( If ([v_IsAdults] = "NA" and [Display rumtime] <= 10 and Not ([Provider] InList ("ABC";"DEF")); [Title]) In ([Title]; [Provider];                       [Display rumtime]); Distinct)

         v_TempIsClips = If ([v_IsAdults] = "NA") then (

                                                                      If ([Display rumtime] <= 10 and Not ([Provider] InList ("ABC";"DEF")); "Clips"; "Others") )

         v_IsClips = Min ( If (Count([v_TempIsClips]) In ([Title]) >  1; "Clips"; [v_TempIsClips]) ForEach ([Title]; [Provider]; [Display rumtime]) )

        The two variable v_TempIsClips and v_IsClips aim to remove clip titles from the rest of raw data. In other words, titles which are counted in Clips              are not counted in the other categories.


   3.  After removing adult titles and clip title, we now have the other titles. count the other titles

         v_count_Others = [v_count_GrandTotal] - [v_count_Adults] - [v_count_Clips]

For other tiltes, it is classified into 3 small groups: Transactional Titles, Premium titles and Free titles.

  1. For Transactional titles, filter titles by these providers: "T01"; "T02"; "T03". Count transactional titles, then remove them from the rest of raw data

      --> v_count_transactional = Count (If ([v_IsClips = "Others" and [Provider] InList ("T01"; "T02"; "T03"); [Title]) In ([Title]; [Provider]); Distinct)

              

               v_TempIsTransactional = If ( [v_IsClips = "Others" ) then (

                                                                                                                        If ( [Provider] InList ("T01"; "T02"; "T03")) then "T"

                                                                                                                        elseif ([Provider] InList ("P01"; "P02")) then "P" )

               v_IsTransactional = Min (If ( Count([v_TempIsTransactional]) In ([Title]) > 1;"T";[v_TempIsTransactional]) ForEach ([Title];[Provider]) )

There is something wrong here and I cannot understand the reason why. Anyone who know why, please help me.

Problem is:

Title A belongs to Clips and the first block gave me a correct result

Titlev_IsClipsv_TempIsTransactionalv_IsTransactional
AClips

when i remove the v_TempIsTransactional, the result goes wrong! I tried to add v_TempIsTransactional into the formula of v_IsTransactional but the result is still wrong.

v_IsTransactional = Min (If ( Count([v_TempIsTransactional]) In ([Title]) > 1;"T";[v_TempIsTransactional]) ForEach ([Title];[Provider];[v_TempIsTransactional) )

Titlev_IsClipsv_IsTransactional
AClipsP

Titlev_IsTransactional
AP

   2.  After removing Transactional tiltes, filter titles by these providers: "P01"; "P02". Count Premium titles, then remove them from the rest of           raw data.

        v_count_Premium = Count (If ( [v_IsTransactional] = "P"; [Title]) In ([Title]; [Provider]); Distinct)

          the count is wrong here because there are titles which belong to Transactional are counted again!

   3.  After removing Transactional titles and Premium titles, we now have Free titles. Count Free titles.

Please help me!

Clip titles

Tags:
Former Member
Not what you were looking for? View more on this topic or Ask a question