Group by clause returning 0 for calculated fields

[ibradly]

Hi,

Can someone please explain to me why my total impressions and impressionslostrank is giving me 0 when I do a group by clause?

Fact:
NoConcatenate
load
Date,
Device,
Engine,
Category,
Campaign,
IsBrand,
IsCoreBrand,
Program,
Geo_Modified,
Country,
Region,
Clicks,
Impressions,
Cost,
SharedDMA,
ClickToCalls,
SIS,
SISLost,
sum(Impressions)/ sum(SIS) as TotalImpressions,
(sum(Impressions)/ sum(SIS)) * sum(SISLost) as ImpressionsLostRank,
Forms, 
DNI_Calls,
resident DCM_Temp3
group By
Date,
Device,
Engine,
Category,
Campaign,
IsBrand,
IsCoreBrand,
Program,
Geo_Modified,
Country,
Region,
Clicks,
Impressions,
Cost,
SharedDMA,
ClickToCalls,
SIS,
SISLost,
Forms, 
DNI_Calls;

Sorry for the long code.

[Digvijay_Singh]

First thing I would say is you should not have SIS, SISLost and impression fields in the group by clause. These fields you are using in aggregation functions.

[ibradly]

I tried that and it gave me invalid expression. So I am not sure what is happening exactly in the back end.

[Digvijay_Singh]

Can you share sample data? I see too many fields in Group By clause but that shouldn't be a problem as long as you have fields in Group By clause having repetitive values, normally these are dimension fields, do you think cost needs to be there in group By clause? 

Do you get any error while script load? If its getting executed properly, I don't see anything wrong if you don't have zeroes in the fields used inside 'SUM'.

[Digvijay_Singh]

The comma after last field in the table load is not allowed I think, are you sure your script is executed or you have right statement in qlik and its just typo here?

Forms,
DNI_Calls,
resident DCM_Temp3

[ibradly]

The comma after DNI Calls is just a typo here. There is no error when i run it in the script loader. However, there are some campaigns that have 0 impressions. You are saying that could be the error?

This is a shorter script of the original dataset.

DCS:
Load
date,
Campaign,
AdGroup,
Engine,
DeviceSegment,
Impressions,
Clicks,
Cost,
SIS,
SISLost,
sum(Impressions)/ sum(SIS) as TotalImpressions,
(sum(Impressions)/ sum(SIS)) * sum(SISLost) as ImpressionsLostRank
resident DCSTemp
group By
date,
Campaign,
AdGroup,
Engine,
DeviceSegment;

When i run this scrip it gives me invalid expressions. When I add impressions, clicks, cost, SIS and SISLost to the group by it runs. Why is that the case?

[Digvijay_Singh]

This script will throw error because each field needs to be mentioned either in the aggregation function or below the group by clause.

I don't see Clicks, Cost used in any of these places. Either you should remove from the field list or mention in the group by clause but it all based on your needs.

[Digvijay_Singh]

I don't think 0 impressions in some rows will be a problem here. Can you share some sample rows.

[ibradly]

This is the error even after i add clicks and cost to the group by.

Here is the sample of the data

[Digvijay_Singh]

I think script is still wrong, try like this - 

DCS:
Load
date,
Campaign,
AdGroup,
Engine,
DeviceSegment,
Clicks,
Cost,
sum(Impressions)/ sum(SIS) as TotalImpressions,
(sum(Impressions)/ sum(SIS)) * sum(SISLost) as ImpressionsLostRank
resident DCSTemp
group By
date,
Campaign,
AdGroup,
Engine,
DeviceSegment,

Clicks,
Cost

;