Set Analysis with Fields Comparison

[Marcoimp]

Hi,

I need to count the distinct Customers comparing a field with another field in order to get the value of some combined elements:

=count({<Comm_Year={$(CurrYear)}, Period={"=TA_Cod"}>} DISTINCT CustomerID)

Where TA_Cod contains;

"G"

"M,G"

"M,G,S"

"S,M"

....etc.

(I load this table with an INLINE script).

I can't find the correct syntax using a field in my set analysis....

Some help?

[Anonymous]

Hi,

Try =count({<Comm_Year={$(CurrYear)}, Period={"=Period =TA_Cod"}>} DISTINCT CustomerID)

Regards,

Greeshma

[jonathandienst]

You cannot use a set expression for line by line comparisons in a chart. If there is a single, value of TA_Cod for each line, then you could:

=count({<Comm_Year={$(CurrYear)}>} DISTINCT If(Period=TA_Cod, CustomerID))

If you are doing this outside of a chart (eg in a text box), then you may need:

=count({<Comm_Year={$(CurrYear)}, Period=P(TA_Cod)>} DISTINCT CustomerID)

HTH

Jonathan

[Marcoimp]

Good idea but....

I need to compare all the posibility, the right function is this:

=count({<Comm_Year={$(CurrYear)}>} DISTINCT If(index(TA_Cod, Period)>0, CustomerID))

It works!

Thank you very much for the help... even if it's very very slow...

Some other idea?

[jagan]

Hi,

Is Period and TA_Cod fields are in the same table?  In that case create a flag in script like below

LOAD

*,

If(Period = TA_Cod,1, 0) AS Flag

FROM DataSource;

Now in frontend

=count({<Comm_Year={$(CurrYear)}, Flag={1}>} DISTINCT CustomerID)

OR

Try

=count({<Comm_Year={$(CurrYear)}, Period=P(TA_Cod)>} DISTINCT CustomerID)

Hope this helps you.

Regards,

Jagan.

[Marcoimp]

No they are in different table.

it seems I can't do in a table, I resolved using some text fields making in this way:

Only 'G': =count({<DataCommerciale_Year={$(CurrYear)}, PeriodicitaComm={G}>} DISTINCT Cliente)

Only 'S': =count({<DataCommerciale_Year={$(CurrYear)}, PeriodicitaComm={S}>} DISTINCT Cliente)

G+S :=count({<DataCommerciale_Year={$(CurrYear)}, PeriodicitaComm={G}>*<DataCommerciale_Year={$(CurrYear)}, PeriodicitaComm={S}>} DISTINCT Cliente)

...

and so on for each combination. M+S+G

=count({<DataCommerciale_Year={$(CurrYear)}, PeriodicitaComm={G}>*<DataCommerciale_Year={$(CurrYear)}, PeriodicitaComm={S}>*<DataCommerciale_Year={$(CurrYear)}, PeriodicitaComm={M}>} DISTINCT Cliente)

In this way I get the right values, combining every PeriodicitaComm on every DISTINCT Cliente