Date prompt use in Formula with DatePart? 2

[supportsvc]

Hello,
Report is to show data for each week based on a date range prompt

I have this formula but it errors saying the {?DateRange} prompt I get an error

"A date is required here." with the {?DateRange} highlighted

 If DataPart("ww", ({TransactionDate}) = DatePart("ww",{?DateRange}) then Sum({TransactionQty}) else 0)

 

[fisheromacs]

Is your data dates or text/string or numeric?

You likely some sort of a compatibility check and then a conversion if it is not a date format.

 

[supportsvc]

Oops, the CODE part got messed up.

Fixed the CODE insert so that the formula can be seen this time

The DateRange prompt is set to Date. Not sure what's wrong and how to fix it.

 

[pmax9999]

Is {?DateRange} parameter a range?

You can't use DatePart() with a range, only an actual date.

You could do the DatePart() test against Maximum({?DateRange}) or Minimum({?DateRange}) or both but without understanding your logic I'm not sure that would be useful.

Hope this helps.

Cheers,
Pete.

 

[supportsvc]

Hello,
Yes, it is a range.

Trying to produce the weekly data based on the date range prompt.

I made a change since I have formulas for the column labels

It's kind of working I think but it's repeating the values for all the weeks??

 

[pmax9999]

Can you explain please how/where the parameter is being used (include any code)?

The 2 screen shots above are obviously formulas - please explain how/where they are being used too.

Pete.

 

[supportsvc]

The {?DateRange} prompts the user for a date range.
From that data set the formulas are deriving the weeks

the 2nd formula is what I'm having issue with as it looks like it's producing the same quantities for each week the data range selected

 

[pmax9999]

Not a lot to go on here. Happy to have a look at the report if you are prepared to post it with saved data.

 

[supportsvc]

Ok thank you

https://files.engineering.com/getfi...2afbc20c5a&file=IM_QtySoldbyWkVend_Custom.RPT

 

[pmax9999]

In your post of the formulas, what is the formula name for the first one?

Is that one of the {@Week__} formula? What is the difference between the various {@Week__} formula versions. If they are all the same that would explain your results I think.

 

[pmax9999]

You are setting the week number based on the transaction date, whereas you should be setting it based on the date range.

I would:
1. replace the Date Range parameter with a single date (representing the end date)
2. determine the start date based on the standard reporting period (eg, 20 weeks)
3. set the week number (in the formulas {@Week__}) based on the end (or start) date parameter.

Let me know if that is enough info to go on. Happy to provide the code in need - just let me know what the 'normal' reporting period is, ie, the number of weeks the report should cover and if there is a specific end day (ie always end on a Friday or a Sunday) or just based on the end date parameter.

Hope this helps
Cheers
Pete

 

[supportsvc]

Not sure if I understand the use of Start or End Date since ...

If the user wants to see each week's total quantity for a time period, the saved data it's 4/1/2022 - 5/31/2022

The labels is displaying the week number based on the date range
So since the label formulas are producing the correct week numbers for the date range, I added that to the 2nd formula to produce the quantity for each week

Does that help?

 

[pmax9999]

Your sample report was run for 2 months, so around 8 weeks, yet your column headings show Week 20, Week 19, Week 18 etc, so don't actually specify the date covered by the week.

You have formulas for weeks 1 to 12; what happens if a date range covers more than 12 week. You have run the report for an 8 week (approx) period, yet it is returning 12 weeks of data. That alone should confirm an error of logic.

I can provide the code to show you how it can work, but you are going to need to answer the questions in my previous response.

 

[pmax9999]

As a starting point, try replacing {IM_ItemTransactionHistory.TransactionDate} with Maximum({?DateRange} in all your {@Week__} formulas.

 

[supportsvc]

The date is based on the week of the transactions that occured.

The Week## is the representation of the Formulas Week01 through Week12
The Week number of the week of the TransactionDate

Still repeating the data in other weeks.

Depending on the date range the user enters the max weeks it'll show is 12 weeks

 

[pmax9999]

The next step is to remove the "-1", "-2", "-3" etc from the {@Wk##Data} formulas, so they look like this:

If      DatePart ("ww", {IM_ItemTransactionHistory.TransactionDate}) = {@Week[COLOR=#EF2929]##[/color]} 
Then    {IM_ItemTransactionHistory.TransactionQty} 
Else    0

where ## is the Week number in the formula name.

Now I could be wrong on the next part as I am not absolutely certain what you are trying to achieve, but I think the {@Wk##Data} formulas should probably be moved to the Details section with a Summary added to the G2 Footer.

My point regarding the 12 Weeks of columns is this: If you are always going to show 12 weeks data, why ask the user to enter 2 dates. Why not have them enter the End Date, then calculate the Start date as being 12 weeks prior.

Anyway, that would be my approach, but you do you.

Hope this helps.
Pete.

 

[pmax9999]

And one final comment I would make is that labelling the columns Week 19, Week 20, Week 21 etc is vague.

While the 2nd to 11th weeks of the report will always be full weeks, the 1st or last week on the report could be as little as 1 day. Comparison with other weeks will therefore mean little.

I would argue that a far smarter way to do it would be to record the "week ended" date rather than the Week number, so as to make it more obvious where the 1st or last week is less than a full week.

The other thing you might want to think about is what happens when you report on a period that ends in the 1st few weeks of the year. Under this situation, you will end up trying to determine if a given date is in "Week -1", or "Week -2" which won't work.

Anyway, just something to think about.

 

[supportsvc]

Hello,
Yes that makes a lot more sense!
Thank you

Why not have them enter the End Date, then calculate the Start date as being 12 weeks prior.

How do I achieve that?


Yes, also agree. How?

ecord the "week ended" date rather than the Week number, so as to make it more obvious where the 1st or last week is less than a full week.

 

[pmax9999]

I have a bit on at the moment so it will be a day or so before I can get back to you.

Are you happy with the weeks starting on a Sunday and Ending on a Saturday which is the Crystal default, or do you have a specific week (eg Monday to Sunday) that works better for you?

Cheers, Pete.

 

[supportsvc]

Hello,
Yes that default is fine.

Thank you so much!