Filter and Count rows by Date

[caslasgee]

Hi,

I have a SharePoint list.

I will like like to populate the year(date) in a Drop-down and display in a label result in a label. I used clear collect to populate the year using the date column from the SharePoint list.

ClearCollect(Dyear,DVisit)

Then i added the following to my Drop-down.

Distinct(Dyear,Year(StartDate))

I added a label but it wouldn't display anything. See formula used below.

CountRows(Filter(DVisit, Dyear = drpdyear.Selected.Value))

Thanks.

[TheRobRush]

try placing a temporary gallery with this as the items 

Filter(DVisit, Dyear = drpdyear.Selected.Value)

and see if it provides results you expected, will help narrow down your problem

[caslasgee]

Hi @TheRobRush , Just tried it and it isn't working and the error goes "Incompatible type of Comparison".

Thanks.

[TheRobRush]

ok now place two labels and have one be 

drpdyear.Selected.Value

and the other 

First(DVisit).Dyear

then show us the result or tell us what they each say

[caslasgee]

Hi @TheRobRush 

 Only this displayed - drpdyear.Selected.Value

The other showed an Error - First(DVisit).Dyear

[TheRobRush]

alright so thats the error we need to investigate. what is DVisit? is it a collection? if you place a gallery and put DVisit as the items to you get a gallery?

if you go here, is it listed in yout collections or datasources?

we will need to figure this stuff out to determine how to properly reference whatevr variable we are trying ot find here

[caslasgee]

@TheRobRush, Yes, DYear is a collection see attached Image below.

I did the collection to populate the Years of the StartDate Column of my SharePoint List in a Drop Down.

And when you put DYear in a gallery Item, It display the SharePoint List.

Thanks.

[TheRobRush]

Ok so, is DVisit a sharepoint list and Dyear a collection that is made FROM that list? If so you don't really need the collection, you could have done it straight from the sharepoint list itself, but that's fine lets figure out what we have here real quick.

from what I have seen so far, try

Filter(
AddColumns(DVisit, "yearCheck", Year(StartDate)),
yearCheck = drpdyear.Selected.Value)

 that in a gallery items and see if it populates correctly

[WarrenBelz]

@caslasgee ,

You have another long discussion on essentially part of the same subject over on this thread.

[caslasgee]

@TheRobRush, After putting the above in my gallery, I was able to access other labels and populates correctly.