topic Re: Problem with aggr(). in QlikView

Problem with aggr().

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Problem with aggr().

sunny_talwar — Wed, 09 Mar 2016 01:49:16 GMT

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))

Re: Problem with aggr().

amayuresh — Wed, 09 Mar 2016 03:36:58 GMT

Try This using Order_Year List box and expression for

Previous Year

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

Re: Problem with aggr().

Anonymous — Wed, 09 Mar 2016 14:23:19 GMT

Thank you Sunny

Re: Problem with aggr().

Anonymous — Wed, 09 Mar 2016 14:23:30 GMT

Thank you Mayuresh

Re: Problem with aggr().

Anonymous — Wed, 09 Mar 2016 17:44:21 GMT

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‌

Re: Problem with aggr().

sunny_talwar — Wed, 09 Mar 2016 18:51:47 GMT

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

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))

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))