Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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))
));
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.
hope I understand your question
dimension
Year(Date) & '-' & Month(Date)
Goods
expression
sum(aggr(count(distinct Receipt),Date,Goods))