Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
PFA.
I need max attrition value wrt head= bcone ,and max(Year) and max month wrt year and month with head bcone
Plz suggest.
Thanks
Hi Deepak, you'll need a date field or at least a numeric month field to do the <= comparison. Right now your Month field is a string.
You can create the numeric month like this:
Num(Month(Date#(Year & ' ' & Month, 'YYYY MMM'))) as MonthNum
Then you can use this field in set analisys instead of Month, using Max() instead of maxstring().
I am using below formula for calculationg max(year)-1
if data for max(year)-1 not there then show max(year)-2 otherwise show max(year)-1
i have year as "Year", month as "month" and year is 2017,2016 and 2015
but the expression not working
Please suggest
(if((Sum({<Head = {'Bcone'},FlagNum = {"$(vAttriationFlagNum)"},Year={'$(=(max(Year)-1))'},Month_Num={'<=$(=max(Month_Num))'}>}Attrition))
=0,
((Sum({<Head = {'Bcone'},FlagNum = {"$(vAttriationFlagNum)"},Year={'$(=(max(Year)-2))'},Month_Num={'<=$(=max(Month_Num))'}>}Attrition))),
((Sum({<Head = {'Bcone'},FlagNum = {"$(vAttriationFlagNum)"},Year={'$(=(max(Year)-1))'},Month_Num={'<=$(=max(Month_Num))'}>}Attrition)))))
Hi Deepak, maybe there is no data? or vAttriationFlagNum returning unexpected value?
To check the variable value
- Add an inpuut box with the variable
or
- Create a table with 2 expressions: one for the year-1 expression and another for year-2 (no dimensions or title for expressions).
- Keep the mouse over the title of expressions to check to value in FlagNum = {"$(vAttriationFlagNum)"}
To check the data
- Make a text box with the expression =Sum(Attrition)
- Manually make the selections done by each expression in set analisys (Head, FlagNum, Year and Month)
Why these extra parenthesys? those only helps to make it harder to read.
