11 Replies Latest reply: Oct 20, 2017 5:20 AM by Ali Bouderbala

# How to generate a calculated field with count ( ) or Sum in the load script ?

Hi experts !

I want to generate a calculated column in the load script to use it in my set analysis

Case :

I want to load this expression as a column

Expression:

=Count({<[State] = {'UK'}>} [Customer])

Example:

=Count({<[State] = {'UK'}>} [Customer]) as UK_CUSTOMERS

Please tell me how can i do that ?

Regards

Ali

• ###### Re: How to generate a calculated field with count ( ) or Sum in the load script ?

One solution could be

Fact:

Right Join (Fact)

Load State, Count(Customer) as UK_CUSTOMERS Resident Fact Where State = 'UK' Group By State;

• ###### Re: How to generate a calculated field with count ( ) or Sum in the load script ?

Thanx Anil , I will try it and i'll tell u if it works

• ###### Re: How to generate a calculated field with count ( ) or Sum in the load script ?

Do you need this count based on another dimension or overall count?

Count(If(State = 'UK', Customer)) as UK_CUSTOMERS

• ###### Re: How to generate a calculated field with count ( ) or Sum in the load script ?

Hi Sunny ,

Pleasure to see u

like the expression used in the set analysis , i want to generate a calculated field with the function count :

exemple :

Column: Data1

Value: Toto

expression: count('Toto')

Needs: To generate a calculated field in the load script based on the expression  count('Toto')

something like  count('Toto') as Test

• ###### Re: How to generate a calculated field with count ( ) or Sum in the load script ?

Are you looking to get the overall count? or count by some dimensions?

• ###### Re: How to generate a calculated field with count ( ) or Sum in the load script ?

Sunny,

I have the two cases Overall count and By dimension

exemple :

Column: Data1

Value: Toto

expression: count('Toto')

Dimension: Clienta

Thanx a lot

• ###### Re: How to generate a calculated field with count ( ) or Sum in the load script ?

Ok Sunny  show me for the Overall count ...

• ###### Re: How to generate a calculated field with count ( ) or Sum in the load script ?

May be like this (using Anil's code but showing that your Fact table can have more than the two fields and doing a Left Join instead of Right Join)

Fact:

Customer,

...

FROM Table;

Left Join (Fact)

Count(Customer) as UK_CUSTOMERS

Resident Fact

Where State = 'UK'

Group By State;

• ###### Re: How to generate a calculated field with count ( ) or Sum in the load script ?

it doesn't work , but i put the complete official script please Have a look and tell me what should i do for the expression :

=Count({<[EtatBitLockerChiffrement] = {'conforme'}>} [Client])/Count([Client])*100

Needs: put the expression in the load script as a Calculated field

=Count({<[EtatBitLockerChiffrement] = {'conforme'}>} [Client])/Count([Client])*100 as TOTO

Security:

Client &'|'&Machine as Key,

OS_Version,

EtatBitLockerChiffrement,

Client,

TypePoste,

ZC_ProductVersion,

ZC_ActiveState,

Age_Def_Antivirus,

Qualif_Age_of_Antivirus,

Machine,

EtatTPMActivationBIOS,

EtatTPMValidationWindows,

EtatTPMPropriete,

DernierEchecFixBitLocker,

DerniereActionRealisee,

SEP_FWRunningStatus,

SEP_LatestVirusDefsDate,

SEP_LatestVirusDefsRevision,

SEP_DeployRunningVersion,

SEP_LastSuccessfulScanDateTime,

SEP_Smc_engine_status,

SEP_PolicyMode,

ZC_NextActiveState,

ZC_InstallDate,

FROM

[..\QVD\QVD_TR\TBD_PDT_SECURITE_SECU_ALL.QVD]

(qvd  );

• ###### Re: How to generate a calculated field with count ( ) or Sum in the load script ?

LEFT JOIN (Security)

100.0*Count(IF (EtatBitLockerChiffrement = 'conforme', Client))/Count(Client) AS TOTO

RESIDENT Security

GROUP BY Cliient;

Best,

Peter

• ###### Re: How to generate a calculated field with count ( ) or Sum in the load script ?

Awesome , Thanx Peter !  I have another question :

i want to use the field TOTO which is a calculated field  with the dimension month  in a bar chart  and show the last six months ?  How can i do that ?