Stuck at wrong count!

[Former Member]

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

[Former Member]

Is there somebody who can help me?