How to get a value from last year month = to this ...

Tiger1 · 11-05-2020 11:58 AM

I need the value of last year revenue of the month = current month.

I then want to take that value and multiply it by .20.

Steve

Try:

([last year revenue of the month = current month] * 0.20)

Tiger1

how do you get the [last year revenue of the month] column?

Meaning - i do not have a virtual nor column saying this. My table is filled with past information - but not one with that column.

Here is my table so far:

I want to be able to show proj revenue based on last year month revenue = current month * .20

Steve

How do you think it would be done?

Tiger1

I am not sure based on my table. I am having trouble getting it to know my current month in relation to that same month a year ago.

Steve

Tiger1

Thanks Steve

Tiger1

Steve i am still struggling on this.

I have created several VC and i cannot seem to get the solution.

I have tried creating:
VC Current Month
VC Current year
VC Last year same month
VC Last year

I was going to then write the expression to give the last year revenue of the same month = to this years month.

I cannot figure this out.

Example of what i need:

We are in the month of November.
So i want 2019 month of November Revenue value. I then want to multiply that by .20

Steve

Like this?

(
  SUM(
    SELECT(
      table[Revenue],
      ((EOMONTH([Date], -1) + 1) = (EOMONTH(TODAY(), -13) + 1))
    )
  )
  * 0.2
)

Replace table with the name of the table.

Tiger1

Steve

Where is that error displayed?

Please post a screenshot of the expression as entered.

Tiger1

That error comes up in the preview and when i “Test” it

Steve

Is the Date column in the app of type Date?

Tiger1

Yes:

Steve

Please use Test from Expression Assistant and post a screenshot of the entire window (minus any browser stuff) showing the error.

Tiger1

Steve

I have no explanation for that! I’m able to do the date comparison just fine.

Are any of your Date column values blank or not dates?

Tiger1

No. But this is the table. We add to this daily. Is the space below a problem?

Steve

No, blank rows are ignored by AppSheet.

I have no idea what’s going wrong. I think you’ll need to engage support@appsheet.com to troubleshoot.

Is spreadsheet column C (the entire column) formatted as plain text?

Tiger1

This?

Steve

Yes. Please format the entire column as plain text, at least for troubleshooting.

Please also delete all blank rows and columns, so that the entirety of the spreadsheet only consists of the columns used by your app and the rows with data.

Tiger1

This?

It didnt help…

Steve

Okay…

My next suggestion is to change the spreadsheet format back to Date, then copy the entire column, then paste the entire column as plain text (paste values only) (Shift+Ctrl+V) to replace the numeric dates with textual dates, then change the column format back to plain text.

Note that the above is destructive, so if you want to be able to revert should this suggestion fail, have a plan for that.

Tiger1

Everytime i change the format to “Text” in my excel sheet - it changes those dates to numbers like before.?

Even after doing what you suggested…

Steve

Ah, okay. Copy the values, then change the format, then paste the values.

Tiger1

Ok. So now it keeps them as dates - but it still didnt change the error in my app:

Steve

Delete the blank rows and columns.

Tiger1

Sorry - how do i delete all rows below the ones with values and the same with the columns?

Steve

Highlight the unused columns, right-click, delete columns. Similar for rows.

Tiger1

It keeps the blank rows and columns in my spreadsheet

Steve

Hmm… Must be a difference between Excel and GSheets.

I gotta admit, I’m at a loss. I think you’ll have to take this to support@appsheet.com.

Sorry!

Tiger1

ok. Thanks for all the help.

Tiger1

Steve,
Instead of trying to get same month last year revenue - How can i just take last month revenue and multiply it by .20 to project future month revenue?

Steve

Likely to have the same problem:

(
  SUM(
    SELECT(
      Revenue table[Revenue],
      ((EOMONTH([Date], -1) + 1) = (EOMONTH(TODAY(), -2) + 1))
    )
  )
  * 0.2
)

How to get a value from last year month = to this current month?