Wrong count

[Former Member]

Hi all,

i have a bunch of titles. they are divided into 3 big groups: Adults, Clips and Others.

Adults titles: if provider is in list ("Adult01";"Adult02";"Adult03")

After removing all Adult titles, clip titles: if lengthInMinutes <= 10 and provider is not in list ("ABC";"DEF")

After removing all Adult titles and Clip titles, we have Other titles.

A title will be considered for Adults firstly. If it is not a Adult title, it will be considered into Clips. Finally, If it is neither Adults nor Clips, it is Others.

In the Others group, I have a rule for classifying them into 3 small groups: Transactional titles, Premium titles and Free titles.

Transactional Title: if provider is in list ("T01"; "T02"; "T03").

After removing all transactional titles, Premium title: if provider is in list ("P01"; "P02")

After removing all transactional titles and premium titles, we have Free titles.

below are the variables I have created to count titles for each:

v_count_GrandTotal = count([Title])

v_count_Adults = count( If ([Provider] InList ("Adult01";"Adult02";"Adult03"); [Title]); Distinct)   -> count titles which belong to Adults

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

Title	v_TempIsAdults	v_IsAdults
A	Adults	Adults
A	NA	Adults
A	NA	Adults
B	NA	NA

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

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

                                                                      If ([LengthInMinutes] <= 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]; [LengthInMinutes]) )

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

each above variables populate the right result.

However, when I count title for Transactional titles, Premium title and Free titles. the result is wrong for Premium titles because there are titles which belong to Transactional are counted again!

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

v_count_Premium = Count (If ([v_IsClips = "Others" and [v_IsTransactional] = "P"; [Title]) In ([Title]; [Provider]); Distinct)  --> wrong here

Is there any solution?

Please help me.

[Former Member]

anyone help me please!