Sum the QTY for consecutive 2 dates only - HELP?

[dmohanty]

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.

[MK_QSL]

Can you explain how you get 14 for both ?

300

2/19

14

14

[dmohanty]

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

[MK_QSL]

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

[MK_QSL]

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))

[dmohanty]

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)

[dmohanty]

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.

[petter]

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