Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
Can you explain how you get 14 for both ?
300 | 2/19 | 14 | 14 |
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
But 100 has also two consecutive dates..!!!
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))
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)
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.
A solution for doing the calculations in a chart could be like this I think - I included an attachment with the example QVW too: