Need to get only one month data

[bharatkishore]

Hi All,

I have a field called MonthYear starting from Jan 2014 to Sep 2017.

Now my problem is if i select any month in 2017 i need to get only Dec 2016 month data.

The same when i select any month in 2016 i need to get only Dec 2015 month data.

The same when i select any month in 2015 i need to get only Dec 2014 month data

Please let me know if you require anything more.

Attached app for more reference.

Thanks,

Bharat

[antoniotiman]

May be this

=If(Count({<MonthYear=P({1<Year={"$(=Year(Max(MonthYear)))"}>})>} DISTINCT MonthYear) = Count(DISTINCT MonthYear),
MonthStart(Min(MonthYear),-1))

[bharatkishore]

Hi Antonio,

Thanks for your reply in the above expression i need to calculate sum(IB) for the same condition. Can you please tell me where i have to include with the condition..

Thanks,

Bharat

[antoniotiman]

In Document Property/Trigger

Set on Field, on Select Field

Field MonthYear

Expression

=If(Count({<MonthYear=P({1<Year={"$(=Year(Max(MonthYear)))"}>})>} DISTINCT MonthYear) = Count(DISTINCT MonthYear),Date(MonthStart(Min(MonthYear),-1),'MM/DD/YYYY'),
'('&Concat(DISTINCT MonthYear,'|')&')')

[bharatkishore]

Thanks Antonio for your reply.

I will explain more in detail sorry if i explain wrong:

I have two fields IB and Month Year. Now in MonthYear i have data from Jan 2014 to Sep 2017. Now what ever year i select it should show sum(IB) only for december.

For example if i select Sep 2017 it should show Dec2016 Sum of IB.

The same if i select any month in 2016 it should show sum of IB for Dec 2015.

The expression what you have given is showing same month. Below image for reference:

[antoniotiman]

Try this

Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Min(MonthYear),-1)),'MM/DD/YYYY'))"}>} IB)

or in Select on Field

=Date(MonthStart(YearEnd(Min(MonthYear),-1)),'MM/DD/YYYY')

[bharatkishore]

Thanks Antonio..

This expression is working perfectly fine:

Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Min(MonthYear),-1)),'MM/DD/YYYY'))"}>} IB)

But only problem is, it is working only  if i select any date the MonthYear field.

Can this work even without selecting monthyear field..

[antoniotiman]

In this case which would be the date to set?

[bharatkishore]

We can assume that, it is this Year which will be Sep 2017...

Sorry if my explanation is wrong..

If the data refreshes for 2018 it should show 2017 dec sum(IB).

defautly if no date is selected then it will be Lastyear 2016 dec sum(IB)

In this scenario we have Sep 2017 data so default it should show 2016 dec sum(IB).. then if we select any month it should show last year december sum(IB).. Based on the selection.

If we don't select anything we should show by default last year 2016 dec sum(IB)..

Then if i make any year for e.g. if i select 2016 any month then it should show sum(IB) for december 2015..

If i remove all the selections then default last year 2016 dec sum(IB)

please let me know if you require anything more.

[antoniotiman · Accepted solution]

May be this

Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Max(MonthYear),-1)),'MM/DD/YYYY'))"}>} IB)

I have changed Min to Max