Hello!
I have a nice report that groups transactions by month, week and day and has defined totals by day, week and month.
The problem I encounter has to do with the fact that there are weeks that start within a month and end within another month: like the last week from January 2012, the 5th from this year: 30.jan and 31.jan are Monday and Tuesday and February starts from Wednesday.
So, for the totaling of January either it includes the first days of February OR for the totaling of February it includes the last days of January OR the 5th week of 2012 is not included neither in January or February…
For retrieving the month I used this:
F17 = NL("Rows=10";"Date";;"Period Type";"Month";"Period Start";"01.01.2012..31.12.2012")
G17 = NF(F17;"Period No.")
H17 = NF(F17;"Period Start")
I17 = NF(F17;"Period End")
J17 = NP("DateFilter";H17;I17) (this is copied up to J26 for grouping purposes)
For retrieving the week I tried this:
A) K18 = NL("Rows=7";"Date";;"Period Type";"Week";"Period Start";J18;"Period End";J18)
This will totaly exclude the 5th week of 2012
B) K18 = NL("Rows=7";"Date";;"Period Type";"Week";"Period Start";J18)
This will include the first days of February within the month of January when totaling by month.
C) K18 = NL("Rows=7";"Date";;"Period Type";"Week";"Period End";J18)
This will include the last days of January within the month of February when totaling by month.
Do you know any solution on how to handle this issue? Or do I have to think of another approach for this report?
If you find it necessary I could attach the actual report to this post.
Thanks,
Elena
Date
Votes
1 comment
-
Jet Reports Historic Posts Cant get DIFFICULTFILTER idea working but in concept this is correct:
DIFFICULTFILTER = OR( AND( NF(,"Period Start")>=D5, NF(,"Period Start")<=E5 ), AND( NF(,"Period End")>=D5, NF(,"Period End")<=E5 ) )Or in normal words give me all weeks where the start or end date is within the start and end date of the month.
Month replicatorC4 = NL("Rows=7","Date",,"Period Type","Month","Period Start","01-01-2012..31-12-2012") D4 = NF(C4,"Period Start") E4 = NF(C4,"Period End") F4 = NP("DateFilter",D4,E4)
Week replicatorD5 = D4 E5 = E4 F5 = NL("Rows=4","Date",,"Period Type","Week",DIFFICULTFILTER,TRUE) G5 = NF(F5,"Period Start") H5 = NF(F5,"Period End") I5 = IF(G5<D5,D5,G5) J5 = IF(H5>E5,E5,H5) K5 = NP("DateFilter",I5,J5)
Day replicatorK6 = K5 L6 = NL("Rows","Date","Period Start","Period Type","Date","Period Start",K6)
Please sign in to leave a comment.