Filtering Data By Date

[LaingBennett]

Hi there everyone,

Struggling to find some existing help on this one in the community so here goes. 

I want to set a control on my page where the data number changes in the green box and the chart changes also. 

This data is using a count of rows in the following table.

How on earth do I get the data to update in the way I want it to.

I am really stuck.

Regards

[RandyHayes]

@LaingBennett 

Sounds good...then you would put the new filter into that formula.

AddColumns(
    GroupBy(
        DropColumns(
            Filter(
                AddColumns(Valuation,
                    "createDate", Text(Date, "yyyymm")
                ),
                IsBlank(PIPELINE_DD.Selected.filter) || StartsWith(createDate, PIPELINE_DD.Selected.Filter)
            ),
            "createDate"
        ),
        "Postcode_x0020_Area", 
        "DATA"
    ),
    "PostcodeCount" , CountRows(DATA)
) 

I believe you are going to run into issues with the naming of your column as "Date" in your sheet.  If so, then consider replacing "Date" in the above to "ThisRecord.Date"

[LaingBennett]

Hi there,

I have put the code into the Items pat of the Bar Chart and updated the column heading in Google Sheets for the date. 

I am still not getting anything to appear in the chart.

[RandyHayes]

@LaingBennett 

Your Text function is not correct. 

If the column in your sheet is "Date", then either use Text(Date, "yyyymm") or (if there is an error with that), Text(ThisRecord.Date, "yyyymm")

[LaingBennett]

I have changed the formula to the following based on the updated column name for date in Google Sheets..

AddColumns(
    GroupBy(
        DropColumns(
            Filter(
                AddColumns(Valuation,
                    "createDate", Text(ThisRecord.This_x0020_Record_x002e_Date, "[$-en-GB]yyyymm"))
                ),
                IsBlank(PIPELINE_DD.Selected.filter) || StartsWith(createDate, PIPELINE_DD.Selected.Filter)
            ),
            "createDate"
        ),
        "Postcode_x0020_Area", 
        "DATA"
    ),
    "PostcodeCount" , CountRows(DATA)
) 

[RandyHayes]

@LaingBennett 

I don't understand why you keep putting  This_x0020_Record_x002e_Date in there....is that the name of your column?  I though it was Date?

[LaingBennett]

I have changed the name of the Date column to This_x0020_Record_x002e_Date as you said I should change it's name. 

[RandyHayes]

@LaingBennett 

Oh...I did not mean for you to change it in your sheet, just in the formula.  But that is okay then (most people flip when I mention renaming a column in their datasource!)

Okay, let me troubleshoot your formulas and data just a little bit so we don't go back and forth forever trying to hunt down what is happening.

Put a Label on the screen and set the Text property to the following:

Text(First(Valuation).This_x0020_Record_x002e_Date, "yyyymm") & " / " & PIPELINE_DD.Selected.filter

Tell me what that shows for each of your selections in the dropdown.

[LaingBennett]

Hi there,

I have added the lable and when the app runs I see:

15/10/2020 10:03:11 / 202010

[RandyHayes]

@LaingBennett 

So it does not appear that the date is actually coming in as a date!

Change the label Text property to the following and let me know the result.

Text(DateValue(First(Valuation).This_x0020_Record_x002e_Date), "yyyymm") & " / " & PIPELINE_DD.Selected.filter

Also, I was reading back because I was assuming SharePoint in this and then google and was wondering where I was confused...so, in re-reading - why are you putting this from SharePoint to google?  You should be able to do all of this from SharePoint.

[LaingBennett]

Hi there,

I am not doing it from the Sharepoint List as when I work with the data the Postcodes do not show in the table and I didn't know why. My work around was to get the data into Google Sheet and they appeared. 

When the data is entered the Postcode is selected from a drop down list. 

I have added what you told me to do and get nothing. 

AddColumns(
    GroupBy(
        DropColumns(
            Filter(
                AddColumns(Valuation,
                    "createDate", Text(DateValue(First(Valuation).This_x0020_Record_x002e_Date), "[$-en-GB]yyyymm") & " / " & PIPELINE_DD.Selected.filter)
                ),
                IsBlank(PIPELINE_DD.Selected.filter) || StartsWith(createDate, PIPELINE_DD.Selected.Filter)
            ),
            "createDate"
        ),
        "Postcode_x0020_Area", 
        "DATA"
    ),
    "PostcodeCount" , CountRows(DATA)
)