Hi, I made an app for our farm to record pla...

expressions
(Valerie Visser) #1

Hi,

I made an app for our farm to record planting, fertilizing, harvesting, etc. all with the common denominator of $/acre.

I made a summary tab with 1 column, listing every single field, and then made lots of virtual columns with planting, etc. using expressions like “SUM(SELECT(Planting Data[Cost per Acre],[Field]=[_THISROW].[Field]))” in the app formula.

When you open up the app, you can select the Field, then all the costs for planting, fertilizing, harvest, etc. are there.

This works!!

Except now that we are on our 2nd year, it is returning all our data from 2017 and 2018, which makes that “at a glance” feature useless.

How can I make it so that after we select “Field”, we can select “Year” and then have only data with that year in it display?

I’m guessing I need a new virtual column, and I’m prepared to redo the rest of the virtual columns.

(They need reordered anyway…)

I appreciate any ideas you can offer!

Valerie

(Aleksi Alkio) #2

Could you use just a simple modification like… SUM(SELECT(Planting Data[Cost per Acre],AND(YEAR(TODAY())=YEAR([Timestamp]),[Field]=[_THISROW].[Field])))

(Fernando López) #3

Just an idea. If you want to separate the data between years, my suggestion is to create a USERSETTINGS parameter called YEAR, and add security filters to all tables when you have a column YEAR (USERSETTINGS(year) = [year])