flags

[ravindraa]

Hi all

  we have one requirement in that requirement we have two files 1.file1

                                                                                             2. file2

in the file1 we have some fields :: contract, customer, business, colorcode , status, .............

in the file2 we have some fields :: contract, customer, business, colorcode , status,.................

status have 5 values :ACTIVE,

                               ACREATED

                               ENROLLED

                               NA

                               CANCELLED

we need to count "contract" based on status wise for this we have wrote logic like this.

this is for one database:::

if(Status='ACTIVE',Count({<Status={ACTIVE}>}Contract),if(Status='CANCELLED',Count({<Status={CANCELLED}>}Contract),if(Status='ENROLLED',Count({<Status={ENROLLED}>}Contract),if(Status='NA',Count({<Status={NA}>}Contract),if(Status='CREATED',Count({<TAStatus={CREATED}>}TContract))))))

we need to count "contract" based on status wise for this we have wrote logic like this.

this is for second   database:::

        if(Status='ACTIVE',Count({<SAStatus={ACTIVE}>}SAContract),if(Status='CANCELLED',Count({<SAStatus={CANCELLED}>}SAContract),if(Status='ENROLLED',Count({<SAStatus={ENROLLED}>}SAContract),IF(Status='CREATED',0,IF(Status='NA',0)))))

using this we get the differentiated value  from both 2 database

here difference value is ::

                                  ACTIVE,       =1500

                               ACREATED     =10    

                               ENROLLED     =2

                               NA                    =6

                               CANCELLED     =3

like this but here if we select 1500 value we need to see all contract list for active like wise for all status i think we need to provide flags for all status but i am not sure if that is right please give any suggestion how it is possible.

if any one know please give suggestions................ it is very urgent............

[#2]

Do you have any transaction ID for each transaction.

[ravindraa]

here we don't have any transaction id's but every customer have one contract based on business.

[#4]

Assuming you are trying to represent the above in a pie, try the below

Dimension

=if(Count({<Status={ACTIVE}>}Contract) >= 0, 'ACTIVE',

if(Count({<Status={ACREATED}>}Contract) >= 0, 'ACREATED',

if(Count({<Status={ENROLLED}>}Contract) >= 0, 'ENROLLED',

if(Count({<Status={NA}>}Contract) >= 0, 'NA',

if(Count({<Status={CANCELLED}>}Contract) >= 0, 'CANCELLED')))))

Expression

count(Contract)

[Roop]

I think that you are right in your supposition that it would be simplest for development to create a bunch of flags and do it in the load script with a statement like:

if (STATUS = 'ACTIVE',

     1,

     0

) as Count_Active,

If(STATUS = 'CANCELLED',

     1,

     0

) as Count_Cancel,

etc. for all others

This would allow you to create a report very quickly with Sum(Count_Cancel) and Sum(Count_Active) and to create percentage figures as well

Hope it helps

[#6]

Create a Calculated dimension

IF(wildmatch(FIELDNAME, '*ACTIVE*', '*ACREATED*','*ENROLLED*','*NA*','*CANCELLED*'), FIELDNAME)

and use the expression as Count (distinct Contract).