Dynamic Charts ... Data Problem
<hr style="color: rgb(235, 235, 235); background-color: rgb(235, 235, 235);" size="1"> <!-- / icon and title --> <!-- message --> <!-- BEGIN TEMPLATE: ad_showthread_firstpost_start --> <!-- END TEMPLATE: ad_showthread_firstpost_start --> I am using an article, "Dynamic Charting By Dates"
http://pubs.logicalexpressions.com/P...cle.asp?ID=246
to create well, dynamic charts ... everything works the way it is supposed to so far but not exactly as I need it to ... I am hoping someone can help me change it slightly ...
I am up to the point of creating the 'Magic Formula' to populate the parallel data (which I have done) and everything works so far as it has been described ... but what it does is take a date for EVERY possible day in between the Start Date and End Date and that works if there is data for EVERY day ... my data isn't every day, at most it would be 5 days out of the week. On the days that there isn't data, it adds the data from what I think is the last date that there was data. So, if I go 3 days without entering data I have 4 entries with the exact same data (the one I entered data for and the 3 that I had no data for that it copied it) ... now, I haven't built the actual charts yet and I may not completely understand the logic involved but if I do a daily chart, I believe, it will give me identical entries for each of those 4 days ... if I understand it, I don't think that is going to work for this application.
My question is this: how do I get the parallel data 'Date' field (and subsequently the data attached to that date) to populate only if there is a corresponding date in the actual data?
If that make sense?
So, the formula in the first 'Date' field in the parallel data is the actual start date that I am beginning at and then the formula to gather the second date is: =IF(NewDate>EndDate,NA(),NewDate)
where NewDate is a named formula that equals: =DATE(YEAR(ActivityGR!$S20)+ActivityGR!YearIncr,MONTH(ActivityGR!$S20)+ActivityGR!MonthIncr,DAY(Acti vityGR!$S20)+ActivityGR!DayIncr)
then the parallel data is calculated by the following formula:
=IF($S20>EndDate,NA(),VLOOKUP($S20,AllData,COLUMN()+1-COLUMN($S20)))
where: S20 is the date associated to that data, AllData is a named reference for all of the data and COLUMN() is something I am not sure what it references, probably the entire column ...
thank you for any help ...
<hr style="color: rgb(235, 235, 235); background-color: rgb(235, 235, 235);" size="1"> <!-- / icon and title --> <!-- message --> <!-- BEGIN TEMPLATE: ad_showthread_firstpost_start --> <!-- END TEMPLATE: ad_showthread_firstpost_start --> I am using an article, "Dynamic Charting By Dates"
http://pubs.logicalexpressions.com/P...cle.asp?ID=246
to create well, dynamic charts ... everything works the way it is supposed to so far but not exactly as I need it to ... I am hoping someone can help me change it slightly ...
I am up to the point of creating the 'Magic Formula' to populate the parallel data (which I have done) and everything works so far as it has been described ... but what it does is take a date for EVERY possible day in between the Start Date and End Date and that works if there is data for EVERY day ... my data isn't every day, at most it would be 5 days out of the week. On the days that there isn't data, it adds the data from what I think is the last date that there was data. So, if I go 3 days without entering data I have 4 entries with the exact same data (the one I entered data for and the 3 that I had no data for that it copied it) ... now, I haven't built the actual charts yet and I may not completely understand the logic involved but if I do a daily chart, I believe, it will give me identical entries for each of those 4 days ... if I understand it, I don't think that is going to work for this application.
My question is this: how do I get the parallel data 'Date' field (and subsequently the data attached to that date) to populate only if there is a corresponding date in the actual data?
If that make sense?
So, the formula in the first 'Date' field in the parallel data is the actual start date that I am beginning at and then the formula to gather the second date is: =IF(NewDate>EndDate,NA(),NewDate)
where NewDate is a named formula that equals: =DATE(YEAR(ActivityGR!$S20)+ActivityGR!YearIncr,MONTH(ActivityGR!$S20)+ActivityGR!MonthIncr,DAY(Acti vityGR!$S20)+ActivityGR!DayIncr)
then the parallel data is calculated by the following formula:
=IF($S20>EndDate,NA(),VLOOKUP($S20,AllData,COLUMN()+1-COLUMN($S20)))
where: S20 is the date associated to that data, AllData is a named reference for all of the data and COLUMN() is something I am not sure what it references, probably the entire column ...
thank you for any help ...
