Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all,
i had a scenario like
=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..
Thanks in Advance
Can you provide sample data or app?
May be try like this:
Count({<Date ={$(=Concat(Chr(39) & Date(AddYears(Today(), -1), 'DateFieldFormatHere) & Chr(39), ', '))}>}billdate)
Can you explain it this piece of code
=Concat(Chr(39) & Date(AddYears(Today(), -1), 'DateFieldFormatHere) & Chr(39), ', ')
Sorry I meant to change Today() to Max(Date)
=Concat(Chr(39) & Date(AddYears(Max(Date), -1), 'DateFieldFormatHere) & Chr(39), ', ')
Chr(39) means can you tell the above function briefly
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
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))
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))
DateNum is floor(DATEFIELD)