Aggregate function with Set Analysis not working

Thu, 05 Dec 2013 12:04:52 GMT

@
Hi,

I've recently been tasked with an exercise to calculate the number of Active customers within SAP.

I've managed to pull the total amount outstanding by writing the following code and it all works perfectly :

num(sum({<Event = {'Due Date'},Cleared_Flag =,[Posting Date] = {"<=$(vDates)"}, Month=, Year=>}if([Clearing Date] >$(vDates),

[Amount in LC_DMBTR],

if(

IsNull(

[Clearing Date])=-1,

[Amount in LC_DMBTR],

if(

len(

[Clearing Date])<4,

[Amount in LC_DMBTR])))),'# ##0')

Where vDates is a variable that contains the selected date.

I then tried to find the number of active customers by taking the above mentioned formula and adding in an aggregate function by Customer, the result should've had been 1000, however i'm returning a value of 848, kindly assist.

Below is my my formula to compute the active customers but it is not seemingly working properly:

sum(aggr(if(sum({<Event = {'Due Date'},Cleared_Flag =,[Posting Date] = {"<=$(vDates)"}, Month=, Year=>}(if([Clearing Date] >$(vDates),

[Amount in LC_DMBTR],

if(

IsNull(

[Clearing Date])=-1,

[Amount in LC_DMBTR],

if(

len(

[Clearing Date])<4,

[Amount in LC_DMBTR])))))<>0,1),

Customer))

topic Aggregate function with Set Analysis not working in QlikView

Aggregate function with Set Analysis not working