topic Re: Sum the QTY for consecutive 2 dates only - HELP? in QlikView

Sum the QTY for consecutive 2 dates only - HELP?

dmohanty — Sat, 18 Apr 2015 15:27:02 GMT

Hi,

I have a data set like this below:

DOC_ID	POST_DATE	QTY
100	2/5	2
100	2/6	5
100	2/7	3
200	2/12	8
200	2/13	4
200	2/16	1
300	2/18	7
300	2/19	7
400	2/22	6

Requirement:

For each ID the Total Qty can be SUM directly.
The QTY_Fill calculation would be like this - Only a single date or if there is a consecutive date too, QTY for those 2 dates need to be SUM.
- Ex - For 100, Total QTY = 2+5+3 = 10 and QTY_Fill = 2+5 = 7
- For 400, Total QTY = 6 , QTY_Fill = 6
OUTPUT would be -
DOC_ID MAX_POST_DATE Total_QTY QTY_Fill
100 2/7 10 7
200 2/16 13 12
300 2/19 14 14
400 2/22 6 6

Please help me in this logic calculation.

Re: Sum the QTY for consecutive 2 dates only - HELP?

MK_QSL — Sat, 18 Apr 2015 17:58:35 GMT

Can you explain how you get 14 for both ?

300

2/19

Re: Sum the QTY for consecutive 2 dates only - HELP?

dmohanty — Sat, 18 Apr 2015 18:02:56 GMT

Hi Manish,

300 has 2 consecutive dates (2/18 and 2/19) - so the QTY has to be SUM for 2 consecutive dates i.e. 7 + 7 = 14.

And also, the Total QTY is also 14 as it has 2 dates only

Re: Sum the QTY for consecutive 2 dates only - HELP?

MK_QSL — Sat, 18 Apr 2015 18:07:36 GMT

But 100 has also two consecutive dates..!!!

Re: Sum the QTY for consecutive 2 dates only - HELP?

MK_QSL — Sat, 18 Apr 2015 18:09:42 GMT

Test:
Load
  DOC_ID,
  Date#(POST_DATE,'M/D') as POST_DATE,
  QTY
Inline
[
  DOC_ID, POST_DATE, QTY
  100, 2/5, 2
  100, 2/6, 5
  100, 2/7, 3
  200, 2/12, 8
  200, 2/13, 4
  200, 2/16, 1
  300, 2/18, 7
  300, 2/19, 7
  400, 2/22, 6
];


Left Join
Load 
  DOC_ID, 
  Date(Max(POST_DATE),'M/D') as MAX_POST_DATE,
  COUNT(POST_DATE) as TOTAL_POST_DATE 
Resident Test
Group By DOC_ID;

Now create a Straight Table

Dimension

DOC_ID

MAX_POST_DATE

Expression

SUM(QTY)

SUM(QTY)-SUM(IF(POST_DATE = MAX_POST_DATE and TOTAL_POST_DATE > 2, QTY, 0))

Re: Sum the QTY for consecutive 2 dates only - HELP?

dmohanty — Sat, 18 Apr 2015 18:11:29 GMT

Yes, 100 and 200 both have two consecutive dates also.

So for 100 - Total QTY = 2+5+3 = 10 (2/5, 2/6, 2/7 - All Dates)

Fill QTY = 2+5 = 7 (calculated for two consecutive dates only - 2/5, 2/6)

For 200 -

Total QTY = 8+4+1 = 13 (2/12, 2/13, 2/16 - All Dates)

Fill QTY = 8+4 = 12 (calculated for two consecutive dates only - 2/12, 2/13)

Re: Sum the QTY for consecutive 2 dates only - HELP?

dmohanty — Sat, 18 Apr 2015 18:14:07 GMT

Thank you for a solution.. Let me check if that helps me.

I was trying if we can calculate the two SUM fields in Load Script itself, without using then in Expressions.

Re: Sum the QTY for consecutive 2 dates only - HELP?

petter — Sat, 18 Apr 2015 22:17:45 GMT

A solution for doing the calculations in a chart could be like this I think - I included an attachment with the example QVW too:

DOC_ID	MAX_POST_DATE	Total_QTY	QTY_Fill
100	2/7	10	7
200	2/16	13	12
300	2/19	14	14
400	2/22	6	6