topic Re: Calculation in Qlikscript in QlikView

Calculation in Qlikscript

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

Hi Every one,

Please help me on below requirement at Script level.

I have loaded the below source data in Qlikview.

LOAD

ID,

name,

"KPI name",

Target,

Actual,

From path...

I need to calculate the Target Revenue, Actual Revenue,Target Profit,Actual Profit as separate fields in the script .

for example Sum({<KPIname={'Revenue'}>}Target) as Target Revenue in the Script.

Thanks in Advance

Re: Calculation in Qlikscript

Anil_Babu_Samineni — Wed, 25 Oct 2017 14:07:15 GMT

may be this?

Sample:

LOAD

ID,

name,

"KPI name",

Target,

Actual

From path...

Concatenate (Sample)

Load ID,

name,

"KPI name",

Sum(Target) as [Target Revenue],

Actual,

Resident Sample Where "KPI name" = 'Revenue' Group By ID, name, "KPI name";

Re: Calculation in Qlikscript

MK9885 — Wed, 25 Oct 2017 14:14:42 GMT

Hi Anil,

I have a question....

I'm doing Count(Distinct(ID))

Count(Distinct(EMPID))

Count(Distinct(ProductID)) and more in front end....

Can I do this in back end by grouping?

Each expression is being used in different chart. some of the fields are are coming from same table like ID and EMPID in same table, ProductID from different table.

the reason I want to do this is I want to avoid this in front end.. as I'm loading 500 M rows and counting it in front end would make it really slow.

any suggestions?

Re: Calculation in Qlikscript

devarasu07 — Wed, 25 Oct 2017 15:26:45 GMT

Hi,

Always try to share source as text/excel that would save our time to help u.

try like below

Data:

LOAD * INLINE [

ID, name, KPI Name, Target, Actual

3301, abc, Revenue, 100000, 800000

3302, def, Profit, 30000, 25000

3303, abc, cost, 20000, 22000

3306, def, Revenue, 100000, 800000

3307, abc, Profit, 300000, 250000

3308, def, cost, 200000, 220000

3311, abc, Revenue, 5500000, 6000000

3312, def, Profit, 1500000, 1300000

3313, abc, cost, 1000000, 1200000

];

RevenueAggr:

load ID,sum(Target) as [Target Revenue], sum(Actual) as [Actual Revenue] Resident Data where [KPI Name]='Revenue' group by ID,name;

ActualAggr:

load ID,sum(Target) as [Target Profit], sum(Actual) as [Actual Profit] Resident Data where [KPI Name]='Profit' group by ID,name;

Re: Calculation in Qlikscript

devarasu07 — Wed, 25 Oct 2017 15:29:16 GMT

Hi,

Yes u do it at back end level using joins/Concatenate method.

Re: Calculation in Qlikscript

MK9885 — Wed, 25 Oct 2017 15:37:02 GMT

Can you please give me an example?

Counting 2 fields from same table using joins?

Re: Calculation in Qlikscript

Anil_Babu_Samineni — Wed, 25 Oct 2017 15:37:12 GMT

Yes, using

Load DISTINCT Field, Count(Measure) as Measure Group By Field,...;

Note - If we are using Group By that means Unique keys can remove and no need to use DISTINCT Keyword for that