I would like to get Entered Date to show as a measure in my Sales database. Is it just a matter of dragging it from the Properties to the Measure?
Not sure if you have to do a transform to force it to a date with no time - I’ve currently got Phocas Development going to do this for me as part of a piece of work.
Later we’ll add Day of Week, Day, Week, Month, Year etc as properties to that dimension - these definitely need a SQL Transform.
Thanks for the quick reply. I already had them add it to the Transaction and it is formatted as a date (no time). Im thinking the I could add it as a measure and it may be able to be shown.
Ah, misunderstood what you were doing, sure it won’t work as a measure.
A dimension yes, but as a measure it won’t be able to sum it or do anything with it - think you’d break Phocas at that point!
I would be asking Phocas to sort it for me.
Just curious @jimmueller, why would you look to change that to a measure instead of a property?
I may be mixing up my terms as we are new to Phocas, but i want to have it in my results as to when it was ordered. Even if invoiced. I need it for a couple reports that i want to filter by yesterday. As an example Incoming for yesterday to me was anything entered yesterday even if it was invoiced. Easy to get Open with an extra of yesterday but not able to add in invoiced and entered the same day. Any guidance is always appreciated…
Thanks!
That sounds like a Stream thing to me. For example, if you have two separate streams for Open Orders and Invoiced Orders, you won’t necessarily be able to pull both lists at the same time, unless you create a combined stream with open and invoiced together. Then, you could run it in Transaction mode and view it by either your defined Period (defined by “Date” in DB Designer), or sort by your “Entered Date” property.
Or, if you’re like me and looking for all invoices in a date range, you could run it on the Invoiced stream in Transaction Mode, download that list to Excel, then run it on the other stream and download that list to Excel, then just copy/paste to combine them.
It is more for the ability at this time to limit a summed total. Incoming Yesterday = Entered yesterday open and invoiced.
In that case, I would suggest a Custom measure, using a calculation in Custom Mode. This is, of course, assuming that both Invoiced and Open streams are in the same database.
Set item “a” to:
Stream: Invoiced
Measure: Dollars?
Format: Actual
Set item “b” to:
Stream: Open
Measure: Same as above
Format: Same as above
Set the Period tab of both to Yesterday, either by a prior defined period, or by selecting “Custom (Day)” and doing an Offset to “-1”.
Finish the calculation by entering “a+b” in the calculation field, set your number format, then press Apply. Your calculation should look like this:
You should then be able to run this column against your Dimensions (customers, buy lines, products, etc.)
1 Like
Tim thanks for the response. The only problem is being able to limit the Invoiced Sales $s to an entry date of yesterday. Incoming would equal open and entered yesterday + entered and invoiced same day (yesterday). Getting the entered and invoiced same day I cont figure out.
Interesting. So you’re really looking to be able to search it as a Property of a Dimension, whereas right now it is probably just a Property at the invoice level. I guess you might be able to make it into a property if you build it that way in Designer? I suppose you could try x-ing out the date within the Properties section, and instead drag and drop that column into the Dimensions area and try it that way. I have never tried it.
I just don’t think you will be able to do this while keeping it as a Property on the invoice level. And I don’t think you can use the same date column that you used for your actual Period Dates, in which case you might want to make a duplicate column using the Transform functionality.
The other thing to make sure of is to make sure you map the column onto the new Dimension in both your Invoiced and Open streams, so you can make that selection in Custom Mode.
Some of this may be confusing if you’re new to Phocas, so I apologize.
Hope it works! Let me know.
We now have the Transaction Date as a dimension, mainly for reporting purposes as I want custom columns but with days down the side.
It gives me the option though of adding to it, Month, Week, Day etc in Properties over time.
I’d start there if I was you.
1 Like
That is exactly what I am after and ties into another report I’m working on showing daily sales for the month. Thanks for the feedback!
We’ve asked for ours like this so that if you span a month end it still sorts correctly (YY-MM-DD):
@jimmueller - were you able to create this incoming sales report? I’m a newer user to Phocas and have the same need. Or is there a more current solution you’re using?