topic Re: Calculates values in a PivotTable in QlikView

Calculates values in a PivotTable

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

Hi guys,

I'm trying to calculate the total amount of money that the company has spent on leads,

by suppliers that the company works with,by day,by country and by product.

In order to display so, I used a pivot table with 2 Cyclic groups,

The problem is:

When I filter: Day=1

and select (in the first cyclic group) the dimension "Supplier Name", I'm getting the result that for 860 leads I spent 1,600$ cost (the Correct result!)!!

and when I select (in the first cyclic group) the dimension "Day" I'm getting the result that for 860 leads I spent 1,120$ cost

(wrong answer)!

Why the result changes when I display the data with another dimension?

The expression I use the calculate the Cost:

=sum(aggr(

if(Match(Leads_SupplierID,1,2,3,4),sum(CostAPI),

sum({<Leads_SupplierID={5}>}Cost * 2) //Google

+sum({<Leads_SupplierID-={5}>}Cost))

,$(=GetCurrentField([Cyclic1])),$(=GetCurrentField([Cyclic2]))))

I have included an example QVW

Thanks in advance

Re: Calculates values in a PivotTable

sunny_talwar — Tue, 07 Aug 2018 11:17:37 GMT

If that is not what you want... then why don't you list all your dimensions

=Sum(Aggr(

If(Match(Leads_SupplierID,1,2,3,4), Sum(CostAPI),

Sum({<Leads_SupplierID={5}>}Cost * 2) //Google

+Sum({<Leads_SupplierID-={5}>}Cost))

, Leads_SupplierName, Day, $(=GetCurrentField([Cyclic2]))))

You might have to do the same thing for your Cyclic2 cycle group

Re: Calculates values in a PivotTable

ttal7878 — Tue, 07 Aug 2018 11:26:01 GMT

why do I have to list all the dimension?

If I use the "GetCurrentField" it shouldn't do the same thing?

Re: Calculates values in a PivotTable

sunny_talwar — Tue, 07 Aug 2018 11:28:40 GMT

Because you seem to be looking to aggregate on SupplierName and Daye even though the current dimension is showing Day... so if you want to do that... then you cannot really use GetCurrentField

Re: Calculates values in a PivotTable

ttal7878 — Tue, 07 Aug 2018 11:41:36 GMT

I want to aggregate only by the display dimension, if the current dimension is "Day" so I would like to aggregate by Day and so on....

the amount of money that has been spent on day 1 is - 1600 $ and not 1120 $

In the original model, I have a lot of dimensions within the cyclin group, it would be impossible to list them all.....

Re: Calculates values in a PivotTable

sunny_talwar — Tue, 07 Aug 2018 11:47:42 GMT

I guess this is because of your if statement... may be use set analysis...

=Sum(Aggr(Sum({<Leads_SupplierID = {1, 2, 3, 4}>} CostAPI) + Sum({<Leads_SupplierID = {5}>} Cost * 2) + Sum({<Leads_SupplierID -= {1, 2, 3, 4, 5}>} Cost), $(=GetCurrentField([Cyclic1])), $(=GetCurrentField([Cyclic2]))))

or just this

Sum({<Leads_SupplierID = {1, 2, 3, 4}>} CostAPI) + Sum({<Leads_SupplierID = {5}>} Cost * 2) + Sum({<Leads_SupplierID -= {1, 2, 3, 4, 5}>} Cost)

Re: Calculates values in a PivotTable

ttal7878 — Tue, 07 Aug 2018 12:03:27 GMT

You are a genius! Thank you so much!

Re: Calculates values in a PivotTable

ttal7878 — Tue, 07 Aug 2018 12:26:48 GMT

stalwar1‌ is it possible to display in the same Pivot table suppliers that have not enter any leads, (have 0 in column count leads) and we spent on them money

for example - supplier BB on day 2 , Count Leads=0 and CostAPI=400

and supplierid 4 on day 1 - Count Leads=0 and costapi=700

I would want to display in column "Leads" Zero and in column cost - the amount of money we spend

Re: Calculates values in a PivotTable

sunny_talwar — Tue, 07 Aug 2018 12:50:11 GMT

Based on the sample provided... what is the expected output you are hoping to see?

Re: Calculates values in a PivotTable

ttal7878 — Tue, 07 Aug 2018 13:32:58 GMT

When you are on the dimension supplier name, two more rows will be added to the table, I have attached an image to the table I want

Re: Calculates values in a PivotTable

sunny_talwar — Tue, 07 Aug 2018 13:45:09 GMT

May be with a little modification in the script

Re: Calculates values in a PivotTable

ttal7878 — Tue, 07 Aug 2018 15:15:46 GMT

Thanks, but sometimes I have cases that leads are entered from a certain supplier and we didn't pay the supplier, meaning there won't be a line in the cost table

I added a row in the leads table, in the attached qvw.

Look at the added line, I want to display the name of the supplier=FF

Re: Calculates values in a PivotTable

ttal7878 — Wed, 08 Aug 2018 10:24:30 GMT

histalwar1‌ do you have an idea?

Re: Calculates values in a PivotTable

sunny_talwar — Wed, 08 Aug 2018 12:01:51 GMT

Checking....

Re: Calculates values in a PivotTable

sunny_talwar — Wed, 08 Aug 2018 12:40:04 GMT

Try this data model

Leads:

LOAD

hash128(Leads_Day,Leads_SupplierID,Leads_Country) as Cost_key,//Key*

LOAD * INLINE [

Leads_Day, Leads_SupplierID, Leads_SupplierName, CountLeads, Leads_Country, Product

1, 1, AA, 30, Australia, X

1, 1, AA, 30, Bahamas, X

1, 2, BB, 100, Bahamas, Y

1, 3, CC, 500, Bolivia, X

1, 5, DD, 200, Australia, Y

2, 5, DD, 10, Bahamas, Y

1, 6, FF, 300, Bahamas, X

];

Cost:

LOAD hash128(Day,SupplierID,Country) as Cost_key,//Key*

LOAD * INLINE [

SupplierID, CostAPI, Cost, Day, Country

1, 100, 20, 1, Australia

1, 200, 0, 1, Bahamas

2, 200, 0, 1, Bahamas

2, 400, 500, 2, Bahamas

3, 500, 200, 1, Bolivia

4, 700, 0, 1, Australia

5, 800, 300, 1, Australia

5, 20, 0, 2, Bahamas

];

LinkTable:

LOAD DISTINCT Cost_key,

SupplierID,

Country,

'Cost' as Flag

Resident Cost;

Left Join (LinkTable)

LOAD DISTINCT Leads_SupplierID as SupplierID,

Leads_SupplierName as SupplierName

Resident Leads;

Concatenate (LinkTable)

LOAD DISTINCT Cost_key,

Leads_SupplierID as SupplierID,

Leads_SupplierName as SupplierName,

Leads_Country as Country,

'Leads' as Flag

Resident Leads;

DROP FieldS Leads_SupplierName, Leads_SupplierID, Leads_Country From Leads;

DROP Fields SupplierID, Country From Cost;

Expressions

Sum(CountLeads)

Sum({<SupplierID = {1, 2, 3, 4}>} CostAPI) + Sum({<SupplierID = {5}>} Cost * 2) + Sum({<SupplierID -= {1, 2, 3, 4, 5}>} Cost)