topic Re: Script Joining Sum Error in QlikView

Script Joining Sum Error

farnsworth — Fri, 09 Jun 2017 10:35:02 GMT

Hi all,

I have the following script running and I get a strange error which I cannot eliminate. The total in the raw data is 10,842 by running with the Report Date the total is 10,842. If it run the total by month not including Report Date I get 10,842. Yet this join means the daily total is 10,842 but the Monthly Total comes out as 10,843 can anyone see what is wrong in this?

NoConcatenate

DAILY:

LOAD

MonthYear

,[Report Date]

,Product

,Channel

,MonthAge

,SUM(Quotes) AS Quotes

,SUM(Sales) AS Sales

,SUM(Day0Sales) AS Day0Sales

RESIDENT

QUOTESANDSALES

GROUP BY

MonthYear

,[Report Date]

,Product

,Channel

,MonthAge

;

//Calculate the total quotes and sales volumes by month

LEFT JOIN (DAILY)

LOAD

MonthYear

,Product

,Channel

,MonthAge

,SUM(Quotes) AS MonthlyQuotes

,SUM(Sales) AS MonthlySales

,SUM(Day0Sales) AS MonthlyDay0Sales

RESIDENT

QUOTESANDSALES

GROUP BY

MonthYear

,Product

,Channel

,MonthAge

;

DROP TABLE QUOTESANDSALES;

Re: Script Joining Sum Error

micheledenardi — Fri, 09 Jun 2017 10:41:14 GMT

Can you upload some example data ?

Re: Script Joining Sum Error

lironbaram — Fri, 09 Jun 2017 10:52:20 GMT

if you do not join the tables

is the Monthly total ok

drop the left join from the script

and if it's ok , then you have duplication in the left join

Re: Script Joining Sum Error

farnsworth — Fri, 09 Jun 2017 14:01:14 GMT

So the join didn't work. Basically what I want to get to is as below

Report Date	Daily	Total	%
01/05/2017	642	10843	5.9%
02/05/2017	687	10843	6.3%
03/05/2017	651	10843	6.0%
04/05/2017	590	10843	5.4%
05/05/2017	606	10843	5.6%
06/05/2017	564	10843	5.2%
07/05/2017	512	10843	4.7%
08/05/2017	670	10843	6.2%
09/05/2017	587	10843	5.4%
10/05/2017	554	10843	5.1%
11/05/2017	541	10843	5.0%
12/05/2017	629	10843	5.8%
13/05/2017	593	10843	5.5%
14/05/2017	472	10843	4.4%
15/05/2017	734	10843	6.8%
16/05/2017	656	10843	6.0%
17/05/2017	608	10843	5.6%
18/05/2017	546	10843	5.0%

Only problem is the total is 10,842 So the calculation I want is Daily / Sum(Daily)