topic Sum number of differences in date range. in QlikView

Sum number of differences in date range.

Sun, 29 Jun 2014 10:20:25 GMT

Hi everyone,

I would like to ask your help in following task.

From below table I would need to count how many times same group of the goods (“A”, “B”, etc) would have differences in receipt value - different receipts, within the same date's range. Basically that would mean that I need to know how many different types of receipts “A” goods would have within certain date period. Data with example of the required answer is down below.

I’m not so good with the script and so far I can not find the correct code to get the required value.

I would appreciate your advice.

Date	Goods	Deal Nr	Receipt
1.06.2014	A	1	105
1.06.2014	A	2	105
1.06.2014	A	3	105
1.06.2014	A	4	105
1.06.2014	B	1	112
1.06.2014	B	2	112
1.06.2014	B	3	112
2.06.2014	A	1	105
2.06.2014	A	2	105
2.06.2014	A	3	105
2.06.2014	A	1	106
2.06.2014	A	2	106
2.06.2014	A	3	106
2.06.2014	B	1	114
2.06.2014	B	2	114
2.06.2014	B	3	114

Required answer:
Date	Goods	Qty
1.06.2014	A	1
1.06.2014	B	1
2.06.2014	A	2
2.06.2014	B	1

Re: Sum number of differences in date range.

maxgro — Sun, 29 Jun 2014 10:30:24 GMT

CHART

2 dimensions

expression is count(distinct Receipt)

SCRIPT

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';

t:
LOAD Date, Goods, [Deal Nr], Receipt
FROM [http://community.qlik.com/thread/123655]
(html, codepage is 1252, embedded labels, table is @1, filters(
Remove(Row, Pos(Top, 24)),
Remove(Row, Pos(Top, 23)),
Remove(Row, Pos(Top, 22)),
Remove(Row, Pos(Top, 21)),
Remove(Row, Pos(Top, 20)),
Remove(Row, Pos(Top, 19)),
Remove(Row, Pos(Top, 18))
));

Re: Sum number of differences in date range.

Sun, 29 Jun 2014 12:43:59 GMT

Hi, Thanks for quick reply.

This part is working. My actual table is a bit bigger and more complicated.

Actually I'm comparing two data bases where I need to compare quantities of "A", "B", "C" etc per year, month, day. And the only trigger to analyze is the number of differences in "Receipt" value. Above is working fine and I can see if "A" or any other type of the goods where repeated. But when I try to sum the number of "A" it is just counting the rows instead of the value.

Could you pls advise how can I get this:

Date	Goods	Qty
06.2014	A	3
06.2014	B	2

Instead of this:

Date	Goods	Qty
06.2014	A	2
06.2014	B	2

How to count total numbers of "A" bases on value of "Qty" within the date range.

Sounds like an easy one, but I'm stuck on it....

Thanks for your help.

Re: Sum number of differences in date range.

maxgro — Sun, 29 Jun 2014 13:41:05 GMT

hope I understand your question

dimension

Year(Date) & '-' & Month(Date)

Goods

expression

sum(aggr(count(distinct Receipt),Date,Goods))