Problem with aggr().

[Anonymous]

sunindia‌

Hi,

Please find the attached qvw file and the screen shot. I’m trying to display the values for Previous year. When the selections are not made in the YEAR, its working fine. When  we select the year then it’s not showing the values for previous year. I think I'm getting the error because of aggr().

please help.

Thanks,

Rajitha

[sunny_talwar · Accepted solution]

Try this expression for previous year:

Sum({<DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_YEAR>}aggr(Count({<DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_YEAR={'$(vPY)'},DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_MONTH={'<=$(vPYMonth)'},

DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.QUARTER=>}DISTINCT(MEM_ID)),DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_MONTH,DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_YEAR,DW_MEM_MONTH_XTRA.Can_Map_Excel.Area2))

[amayuresh]

Try This using Order_Year List box and expression for

Previous Year

=sum({<Order_Year={"$(=Max(Order_Year)-1)"}>} SalesAmount)

[Anonymous]

Thank you Sunny

[Anonymous]

Thank you Mayuresh

[Anonymous]

Hi Bro... The solution which you gave me is working absolutely fin. But the problem is when I'm making selection in the month, The values are changing. As it is YTD calculation it should only when the year is selected... Please help  sunindia‌

[sunny_talwar]

I don't understand why the values won't change when you make a selection in month? YTD means year to date. if you select March then I would expect to see numbers for Jan, Feb and March. If that is not what you want, we can ignore any selection in month as well

PY

Sum({<DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_YEAR, DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_MONTH>}aggr(Count({<DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_YEAR={'$(vPY)'},DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_MONTH=,

DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.QUARTER=>}DISTINCT(MEM_ID)),DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_MONTH,DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_YEAR,DW_MEM_MONTH_XTRA.Can_Map_Excel.Area2))

CY

Sum({<DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_MONTH=>}aggr(Count({<DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_YEAR={'$(vCY)'},DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_MONTH=,DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.QUARTER=>}

DISTINCT(MEM_ID)),DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_MONTH,DW_MEM_MONTH_XTRA.DW_MEM_MONTH_TEMP.MEM_YEAR,DW_MEM_MONTH_XTRA.Can_Map_Excel.Area2))