By using this website, you agree to our Terms of Use (click here)
Hi
Do any of you have any idea how to achieve this in a generic inquiry:
I would like to use the ARTran table and group it by Inventory ID(one row per item). In the inquiry I want to filter based on the period. In the columns I want to see Inventory ID, Sold quantity and Accumulated quantity.
Example:
In period 01-2018 I have sold 2 pieces of item X
In period 02-2018 I have sold 3 pieces of item X
In period 03-2018 I have sold 4 pieces of item X
In period 04-2018 I have sold 5 pieces of item X
Filter; Start period: 04-2018, End period: 04-2018
GI would show:
Inventory ID: X, Sold quantity: 5, Accumulated quantity: 14
So Sold quantity shows the quantity of the period in the filter and the Accumulated quantity shows the quantity from the first period(01-2018) until the End period in the filter.
You can do this with a pivot table and probably with a GI using grouping. The trick is to filter the quantity sold on each line for two columns.
column 1: Item Id
column 2: Item description
Column 3: Date from parameter value
Column 4: quantity sold if invoice date >= start of the financial period and <= end of financial period - Aggregate: sum
Column 5: quantity sold if invoice data >= start of the financial year and <= end of financial year - Aggregate: sum
group by column 1, 2, and 3.
This is a sample of the data field value for a calculated column to determine in an invoice balance is over 90 days old but less than 120 days old
=Switch(DateDiff('d',[ARInvoice.DueDate],Today())>90 and DateDiff('d',[ARInvoice.DueDate],Today())<121,[ARInvoice.CuryDocBal],0)
You will need to look at the function to determine how to address period management and year management.
Tim Rodman, is it possible to use the same @ variables in a calculation in a column as are available in calculations in parameter defaults such as @PeriodStart or @YearEnd?
I can't think of a way to do this in a Generic Inquiry unfortunately.
This is the kind of thing that Power BI measures are good at.
Ok, thanks for the reply!
Brilliant @shawn-slavin! I think your idea will work.
Regarding your question, I'd never tried it before, but I just tried @PeriodStart and got an error so my guess is "no". But you could accomplish the same thing with the DateAdd function.
For the first of the month, you could build your own date with this formula (assuming United States format here):
=CDate(CStr(Month(Today()))+'/01/'+CStr(Year(Today())))
And this formula for the end of the month:
=DateAdd(DateAdd(CDate(CStr(Month(Today()))+'/01/'+CStr(Year(Today()))),'d',-1),'M',1)
Tim, I didn't think the @function would work since @me doesn't work either. However, your formula should work just as well. If VJW will replace the Today() function with a parameter value, the will get a more dynamic and focused result.
Online Members
