Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for
Did you mean:
Creator

## 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

1 Solution

Accepted Solutions
Partner - Champion III

LEFT JOIN (Security)

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

RESIDENT Security

GROUP BY Cliient;

Best,

Peter

11 Replies

One solution could be

Fact:

Right Join (Fact)

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator
Author

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

MVP

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

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

Creator
Author

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

MVP

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

Creator
Author

Sunny,

I have the two cases Overall count and By dimension

exemple :

Column: Data1

Value: Toto

expression: count('Toto')

Dimension: Clienta

Thanx a lot

Creator
Author

Ok Sunny  show me for the Overall count ...

MVP

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;

Creator
Author

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,