20 Replies Latest reply: May 6, 2016 6:49 AM by vijaykrishnamraju vegesna

# get the selected working days for previous year

hi all,

=if(count({<SalesFy={\$(=max(SalesFy)-1)}>}billnumber)>0,count({<SalesFy={\$(=max(SalesFy)-1)}>billdate))

The above query working fine financial year,month

but when i select some 4 dates in current year i need to get the same month four dates for previous year

this is for current year and previous year sales comparsion purpose and also for to see the working days count for current and previous year..

• ###### Re: get the selected working days for previous year

Can you provide sample data or app?

• ###### Re: get the selected working days for previous year

May be try like this:

Count({<Date ={\$(=Concat(Chr(39) & Date(AddYears(Today(), -1), 'DateFieldFormatHere) & Chr(39), ', '))}>}billdate)

• ###### Re: get the selected working days for previous year

Can you explain it this piece of code

=Concat(Chr(39) & Date(AddYears(Today(), -1), 'DateFieldFormatHere) & Chr(39), ', ')

• ###### Re: get the selected working days for previous year

Sorry I meant to change Today() to Max(Date)

=Concat(Chr(39) & Date(AddYears(Max(Date), -1), 'DateFieldFormatHere) & Chr(39), ', ')

• ###### Re: get the selected working days for previous year

Chr(39) means can you tell the above function briefly

• ###### Re: get the selected working days for previous year

Chr(39) is another way of writing a single Quote (').

I am concatenating the list of dates (comma separated) you selected this year and finding the corresponding dates for them in the last year

• ###### Re: get the selected working days for previous year

i Tried it see it's not showing anything to me

=if(Count({<DateNum={\$(=Concat(Chr(39) & Date(AddYears(Max(DateNum), -1), 'MM/DD/YYYY) & Chr(39), ', '))},ITEM={'GOLD'}>}[MASTER REFERENCE CATEGORY CODE])>0,count(distinct {<DateNum={\$(=Concat(Chr(39) & Date(AddYears(Max(DateNum), -1), 'MM/DD/YYYY) & Chr(39), ', '))},ITEM={'GOLD'}>}BILLDATE))

• ###### Re: get the selected working days for previous year

DateNum is in number format? If it is, then try this:

=If(Count({<DateNum={\$(=Concat(Num(AddYears(Max(DateNum), -1)), ', '))},ITEM={'GOLD'}>}[MASTER REFERENCE CATEGORY CODE]) > 0, Count(Distinct {<DateNum={\$(=Concat(Num(AddYears(Max(DateNum), -1)), ', '))},ITEM={'GOLD'}>} BILLDATE))

• ###### Re: get the selected working days for previous year

DateNum is floor(DATEFIELD)

• ###### Re: get the selected working days for previous year

Still it's not working

:-(

• ###### Re: get the selected working days for previous year

Would you be able to share a sample?

• ###### Re: get the selected working days for previous year

Actually do you have any other date and time related field selected? like year? If you do, ignore them in your expression ->

=If(Count({<DateNum={\$(=Concat(Num(AddYears(Max(DateNum), -1)), ', '))},ITEM={'GOLD'}, Year, Quarter, QuarterYear, Month, MonthYear>}[MASTER REFERENCE CATEGORY CODE]) > 0, Count(Distinct {<DateNum={\$(=Concat(Num(AddYears(Max(DateNum), -1)), ', '))},ITEM={'GOLD'}, Year, Quarter, QuarterYear, Month, MonthYear>} BILLDATE))

• ###### Re: get the selected working days for previous year

ya DATEFIELD is there....

if i select 4 dates in datefield i am not getting previous year dates for this column

• ###### Re: get the selected working days for previous year

You have DATEFIELD and DateNum? I guess ignore selection in DATEFIELD also then

• ###### Re: get the selected working days for previous year

It's necessary to me that date field is the selection object whether the  selected dates are there in current year and previous year i want that count....

for example i select feb 29

my current year working days count will get 1

but for previous year working days count will get 0

like that i want count..

I hope you understand my problem