Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
vinay_bangari
Specialist III
Specialist III

Problem with aggr().

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.

vpynot.png

vpyyear.png

Thanks,

Rajitha

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

6 Replies
sunny_talwar

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
Creator III
Creator III

Try This using Order_Year List box and expression for

Previous Year

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

vinay_bangari
Specialist III
Specialist III
Author

Thank you Sunny

vinay_bangari
Specialist III
Specialist III
Author

Thank you Mayuresh

vinay_bangari
Specialist III
Specialist III
Author

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