Set Analysis - Date Range and Total Value Greater than

[ivandrago]

Hi,

i have the attached Document

If I change the value on vCredit to 40, I would expect the only CustNo to return would be CU001 and CU002.

This because for CustNo:

CU001 there are two OrderId's A001 and A004 where there are four OrderLine's before the date of the 01st September for the EgDate and the Sum Total of the Distinct CreditLim A001 = 38 and A004 = 60  which comes to a Total Credit of 98, it shows 60 because my exprssion is set in a way it will only sum the credit per OrderId how do I filter it to add the total sum for the CustNo?

CU002 there are one OrderId A005 where there are two OrderLine's before the date of the 01st September for the EgDate and the Sum Total of the Distinct CreditLim A005 = 80  which comes to a Total Credit of 80 whch it is returning fine.

Thanks

[Anil_Babu_Samineni]

Can you aggregate on CustNo instead OrderId

[vishsaggi]

Why the CustNo should not return CU004?

[vishsaggi]

If yes, then follow Anil's suggestion of adding CustNo to your aggregation in place of OrderId.You should see the total.

[ivandrago]

Hi,

Thanks for the reply.

It will not work when I Group By CustId, if you look at the attached I have added a new OrderId A007 and OrderLine 1 where this has the same CredLim and EgDate as OrderId A001 and OrderLine 2, it seems to be picking the one value where it needs to add together both values. The CredLim will be the same Value across the whole OrderId.

The first table is bringing the correct results but when I apply a value of 40 to the variable vCredit it is filter on the individual OrderId.

I kind of want a SUM and then SUM again over the first result set from the set analysis and then apply the vatiable vCredit to the latest SUM if you know what I mean?

Thanks

[ivandrago]

Anyone out there that thinks this can be done?

[vishsaggi · Accepted solution]

Can you let me know why you should not get CU004 customer? He has one orderline before 1st sep 2017 with a creditlm as 80.

Try this in your expression?

= IF(sum(DISTINCT {$< EgDate = {"$(='<' & TimeStamp(Floor(MonthStart(Today())), 'DD/MM/YYYY hh:mm:ss'))"}>} CredLim) >= $(vCredit),

     sum(DISTINCT {$< EgDate = {"$(='<' & TimeStamp(Floor(MonthStart(Today())), 'DD/MM/YYYY hh:mm:ss'))"}>} CredLim))