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)

 

[supportsvc]

Can you have it from the Start Date and 12 weeks from the Start Date instead?
or provide both just in case

 

[pmax9999]

We could go forward from the start date instead of back from the end date, but the to get the maximum coverage of 12 weeks, the user would need to calculate the parameter date. I would have thought it better that the user just enters a recent date.

Let me know what you think.

Pete

 

[pmax9999]

Ok, so this is what I have come up with.

The report will require a new Parameter which I have called {?End Date}. The report will return data for the 12 Full Weeks up to the Saturday prior to {?End Date}.

Below is a list of formulas, and the code to be entered in those formulas. All are existing Formulas within your report, except for {@Start Date} which is new. If you give use the same Formula Names as I have, you should be able to copy the code straight from here into the formula editor.

[pre]
Formula Name Code
{@Week12} {?End Date} - DayOfWeek({?End Date})
{@Week11} {@Week12} - 7
{@Week10} {@Week11} - 7
{@Week09} {@Week10} - 7
{@Week08} {@Week09} - 7
{@Week07} {@Week08} - 7
{@Week06} {@Week07} - 7
{@Week05} {@Week06} - 7
{@Week04} {@Week05} - 7
{@Week03} {@Week04} - 7
{@Week02} {@Week03} - 7
{@Week01} {@Week02} - 7
{@Start Date} {@Week01} - 7

{Wk01Data} If {IM_ItemTransactionHistory.TransactionDate}) > {@StartDate} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week01}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0

{Wk02Data} If {IM_ItemTransactionHistory.TransactionDate}) > {@Week01} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week02}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0

{Wk03Data} If {IM_ItemTransactionHistory.TransactionDate}) > {@Week02} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week03}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0

{Wk04Data} If {IM_ItemTransactionHistory.TransactionDate}) > {@Week03} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week04}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0

{Wk05Data} If {IM_ItemTransactionHistory.TransactionDate}) > {@Week04} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week05}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0

{Wk06Data} If {IM_ItemTransactionHistory.TransactionDate}) > {@Week05} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week06}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0

{Wk07Data} If {IM_ItemTransactionHistory.TransactionDate}) > {@Week06} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week07}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0

{Wk08Data} If {IM_ItemTransactionHistory.TransactionDate}) > {@Week07} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week08}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0

{Wk09Data} If {IM_ItemTransactionHistory.TransactionDate}) > {@Week08} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week09}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0

{Wk10Data} If {IM_ItemTransactionHistory.TransactionDate}) > {@Week09} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week10}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0

{Wk11Data} If {IM_ItemTransactio <=,nHistory.TransactionDate}) > {@Week10} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week11}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0

{Wk12Data} If {IM_ItemTransactionHistory.TransactionDate}) > {@Week11} and
{IM_ItemTransactionHistory.TransactionDate}) <= {@Week12}
Then {IM_ItemTransactionHistory.TransactionQty}
Else 0
[/pre]

Replace the last line of your Record Selection formula with the following:

{IM_ItemTransactionHistory.TransactionDate} > {@Start Date} and
{IM_ItemTransactionHistory.TransactionDate} <= {@Week12}

The Formulas {@Week01} ==> {@Week12} are intended to be the Column Headings, and represent the "Week Ending" date.

Obviously, without a connection to relevant data I cannot be absolutely certain that there is not a typo in the code.

Hope this helps.

Regards
Pete.

 

[supportsvc]

Hello,
Thank you so much!

Got the formulas and such updated

Please see attached updated report.

The Column headers do not match the date range

 

[MenuItem]

I'm sure you're doing a "manual crosstab" for a good reason, but it's worth pointing out that you could also use a traditional crosstab.

Just summarize TransactionQty, and group columns by either "Week" or "WeekBeginning".
You can have multiple row headings, suppress subtotals for things like UnitCost, and leave them on for those that you're grouping.

@Week:
datepart("ww",{IM_ItemTransactionHistory.TransactionDate})

@WeekBeginning:
DATEADD("d", - (1 * DATEPART("w", {IM_ItemTransactionHistory.TransactionDate}))) + 1,{IM_ItemTransactionHistory.TransactionDate})

 

[supportsvc]

@MenuItem,

Thank you

I did try the built-in Crosstab, however I couldn't figure out to get all the rows into one line as shown in the report.

 

[pmax9999]

You haven't actually followed my instructions, which were based around an End Date parameter, instead using a Start Date parameter which will require additional changes.

 

[pmax9999]

And in relation to Cross Tabs, I wouldn't have used them in this case either.

They are great for simple summarisation but not well suited to this situation. Realistically, the only time I use them is for providing a variety of summaries from the same data set.

 

[supportsvc]

@pmax9999,

I think I'm confused?
I have a new {?EndDate} parameter

 

[pmax9999]

Not sure I understand. What do you mean?

 

[MenuItem]

@supportsvc,

To display all row headings on the same row in a crosstab, right-click the empty space in the top-left corner of the crosstab, click the "Customize Style" tab, and in the "Rows" section, highlight each row heading and check the "Suppress Subtotal" checkbox below.

Just for fun, you can do a simple test by dropping a new crosstab into a duplicated report footer.

Cheers

 

[supportsvc]

@pmax9999

Did I miss something?

I added this to the Report Selection

{IM_ItemTransactionHistory.TransactionDate} > {@Start Date} and
{IM_ItemTransactionHistory.TransactionDate} <= {@Week12}

Where

{@Start Date}	{@Week01} - 7

 

[pmax9999]

No, that's correct.

Has the report been fixed since the last version you uploaded? The formula for {@Wk01Data} referenced your Start Date parameter rather than the formula called {@Start Date}.

 

[supportsvc]

Yes I noticed that part and updated to use the formula {@StarDate} (misspelled it) but still the same result it seems and weeks are showing from March?

 

[pmax9999]

Can you upload the latest report version for me to take a look at please.

 

[supportsvc]

Yes thank you

See attached

 

[pmax9999]

Your Record Selection and the {@Wk01Data} formula both use the Start Date parameter, not the Start Date formula specified. My approach uses a single End Date parameter because it is the most logical approach, for the reasons explained in one of my earlier posts.

Happy to continue to work with you to get my approach working but don't have the time to completely re-engineer the approach. If you want to persist with a starting date instead of an end date, every formula will need to be changed to work forward from the starting point instead of back from the end point.

Haven't looked any deeper than that, but fix those 2 issues and see what happens.

Hope this helps.

Pete.

 

[supportsvc]

Ok that's one thing I got confused on with the Start Date
I can change that to use the Formula no problem

 

[supportsvc]

Same results replacing it with the Formula StarDate

 

[pmax9999]

Sorry, but you are going to need to repost the file so I can see the issue.