topic Re: getselectedcount - with time bracket in App Development

getselectedcount - with time bracket

joeybird — Fri, 29 Jul 2016 08:48:59 GMT

I am using the following expression on a measure on a pivot table.

sum({$<[EventDate.CalendarMain.Date]={">=$(=Date(Today()-6))<=$(=Max(EventDate))"}>}[Sales])

however I need to be able to select an event date from a filter pane, and the pivot table will thus show - 6 days from the selected event date.

I have tried to use

getselectedcount within a sort expression on EventDate , I cannot get this to work.

If( GetSelectedCount(EventDate), Max(FinancialYear), (EventDate)-6) )

please help

Re: getselectedcount - with time bracket

rubenmarin — Fri, 29 Jul 2016 08:53:34 GMT

Hi Joanna, can you try this?:

sum({$<[EventDate.CalendarMain.Date]={">=$(=Date(Max(EventDate)-6))<=$(=Max(EventDate))"}>}[Sales])

Re: getselectedcount - with time bracket

sunny_talwar — Fri, 29 Jul 2016 08:56:07 GMT

Have you tried this:

Sum({$<[EventDate.CalendarMain.Date]={">=$(=Date(If(GetSelectedCount(EventDate), Max(FinancialYear), Max(EventDate)-6)))<=$(=Max(EventDate))"}>}[Sales])

Re: getselectedcount - with time bracket

joeybird — Fri, 29 Jul 2016 13:40:54 GMT

these above don't work

there's nothing wrong with my calculation

its just when i need to select a different event date from the filter pane, i would like the pivot table to select the next 6 days previous to date chosen in filter pane.

i need to place something in the event date sort by expression like

If( GetSelectedCount({<[EventDate.CalendarMain.Date]={">=$(=Date(Today()-6))<=$(=Max(EventDate))"}>}EventDate), Max(FinancialYear), EventDate)

but i cannot get it to work

please help

Re: getselectedcount - with time bracket

rubenmarin — Fri, 29 Jul 2016 15:34:08 GMT

Hi Joanna, Today()-6 is 23/07/2016, if you have a fixed "Today()-6" and the user select some date before that one it will show zero, because there will be no date upper than 23/07 and lower at the same time, that's why you need to use the selected date in both places (>= and <=).

About whyit doesn't works I will need a sample to check date formats.

Re: getselectedcount - with time bracket

joeybird — Tue, 02 Aug 2016 12:36:29 GMT

I need the chart as soon as you load the sheet show last 6 days...then if a user choses a event date, it will show the data for 6 days from the chosen date

I don't get 0 I just get the count for the single day (EventDate) chosen

please help

Re: getselectedcount - with time bracket

rubenmarin — Tue, 02 Aug 2016 13:03:50 GMT

Hi Joanna, if you select '01/06/2016' (or any other date of june) as filtered event date it shows one day? how can any date accomplish the condition >=27/07/2016 and <=01/06/2016?

I will need a sample to check and give an answer.