LinkedIn
Twitter
Could someone let me know why the expression below is not producing the desired results?
Virtual Column = If( [BillingPeriod]=“10/1/2019”, Any(Select(invoicehistory[Previous],([InvoiceNumber]=[_ThisRow].[InvoiceNumber],[BillingPeriod]=“9/1/2019”) )),0)
I would like to retrieve [Previous] from the invoicehistory Table for the previous month for each Unique Row (InoviceNumber is Key) for the month of September 1 2019 and place it on a Virutal Column which will be used to compare values with the [Previous] Column for the month of October 1 2019.
On the below screenshot you can see that the result given by the above expression is 17,070 which is the value corresponding for the month of July 1 2019??? And then on the very last column (ie the Virtual Column) you do see the correct value for the month of September 1 2019 which is 17,169. However, the next value which should have been retrieved should be a different value. In other words, the result from the expression retrieves only the first row for the month of September 1 2019… but the 2nd, 3rd, 4th and nth rows values are not being retrieved. As you can see from the screenshot all the retrieved values are the same, 17,169.
Aleksi, This worked! Please review attached screenshot. I used [UnitNumber] which is the same on both “9/1/2019” and “10/1/2019” instead of [InvoiceNumber] which is a unique key and it worked!
Could you let me know if this is the best way / more efficient way to retrieve the desired recordset?
Thanks Aleksi. I tried adding the AND() but it still doesn’t work. Please review attached screenshoot.
What I am thinking is happening based on result set, is that the IF statement is checking each row where [BillingPeriod] = “10/1/2019” correctly. However, the Select Statement returns the first Row that matches the criteria AND([InvoiceNumber]=[_ThisRow].[InvoiceNumber],[BillingPeriod]=“9/1/2019”) all the time. I was assuming that AppSheet will recognize the correct “rowcount/ rowreference” situation and advance to the next row automatically with each IF Statement, but based on the result set the Select Statement doesn’t automatically advance to the next row and this is why we get 17,169 for all rows in “10/1/2019”.
Is there a work around this scenario? Can I use a Variable to hold initial and current row count, ie RowCount = _RowNumber + 1, these way on the Select Statement the condition will look as follows:
AND([_RowNumber]=[RowCount],[BillingPeriod]=“9/1/2019”). Or can I use a column with identical values on “9/1/2019” and “10/1/2019” instead of using a counter? Kind of like a VLookUp in Google Sheets or Excel.
