Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Geniuses,
I want to find out that in which month of the current year the lowest invoice processed excluding the current month
I am using the below formula to calculate the number of invoice processed
=min(aggr(num((count(distinct{<YEAR={$(=vYearToday)},MONTH-={$(=vMonthToday)}>} INVOICE_NUMBER)),'##0'),MONTH))
This is working fine and give me the correct result which is 1952
Now I want in which month this 1952 is coming from for that I am trying to use the below formula
=FirstSortedValue(MONTH,(aggr(num((count(distinct{<YEAR={$(=vYearToday)},MONTH-={$(=vMonthToday)}>} INVOICE_NUMBER)),'##0'),MONTH)))
In this formula as soon as I use the MONTH in the set analysis it is not returning me anything(highlighted in bold), why I am using the MONTH with -= as I want to exclude the current month, please help me out to solve this
vYearToday =Year(today())
vMonthToday =Month(Today())
Thanks in advance,
S
You can try this:
FirstSortedValue(MONTH,(aggr(num((count(distinct{<YEAR={$(=vYearToday)},MONTH={"<$(=vMonthToday)"}>} INVOICE_NUMBER)),'##0'),MONTH)))
It is not working
Please check what is variable is returning.
Format returned by Variable and that of Values from Month Field Should match.
also possible share screen shot of what output you are getting after evaluating the variable and values of Filter Month
Can you post sample?
Please let me know whether this is returning anything for you?
=FirstSortedValue(MONTH, Aggr(count(distinct INVOICE_NUMBER),MONTH))
Then use this?
=FirstSortedValue(MONTH, Aggr(count({<YEAR={$(=vYearToday)},MONTH={"<$($(=vMonthToday))"}>} distinct INVOICE_NUMBER),MONTH))
attached is the screenshot
I am getting no value after putting the formula you gave(left text box shows same), Month is coming as Jan Feb Mar and the vMonthToday variable is returning Sep shown in the right text box
I think the problem is with the first sorted value
Yes Anil the first expression returning me Sep,
I have tried the expression you gave but it is not returning anything
I think the problem is with the firstsortedvalue
AFAIK, If first one returns some thing then how come FirstSortedValue is wrong. Could be problem with variables? You can work on variables or share sample to test
Anil I did some analysis and found out that I have two month in which the invoices are the lowest and are exact same as 1952 so the firstsorted value is getting confused to sort it, please tell me now how to tackle such situation