Skip to Content
avatar image
Former Member

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.


Title v_TempIsAdults v_IsAdults A Adutls Adults A NA Adults A NA

Adults

B NA NA


   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

Title v_IsClips v_TempIsTransactional v_IsTransactional A Clips

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

Title v_IsClips v_IsTransactional A Clips P

Title v_IsTransactional A P

   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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Jan 05, 2015 at 03:37 AM

    Is there somebody who can help me?

    Add comment
    10|10000 characters needed characters exceeded