sales calculation if condition

[#1]

Hi Experts,

i have the following tables,

CUSTOMER:

LOAD Branch,

         CUSTNO,

     Total_Target,

     Daily_Target

FROM

QVD\CUSTOMER.qvd

(qvd);

SALES:

LOAD Branch,

   Date,

   Branch as Branch1,

       Date as Date1,

     CUSTNO,

     SalesValue

FROM

QVD\Sales.qvd

(qvd);

WorkingDays:

  LOAD  Branch as Branch1,

             Date as Date1,

      WorkingDays

        FROM

        [\\QVDs\CALENDAR.qvd]

        (qvd)

And also i have master calendar associated with sales table.

i have to give the summary of sales like below

i need to use the condition like,

if (sales/workingdays) > Daily_Target , then only for that customers, the sales summary has to be dispalyed on the table. but the table shouldn't include CUSTNO as dimension.

How can i do this.

pls help me on this

[kaushiknsolanki]

Hi,

Kindly update some sample data.

Regards,

Kaushik Solanki

[jagan · Accepted solution]

Hi,

Try like this using Aggr()

Dimension: Month

Expression:

Total Sales: Sum(Aggr(if (Sum(sales)/Sum(workingdays) > Only(Daily_Target), Sum(Sales)), Customer))

Working Days: Sum(Aggr(if (Sum(sales)/Sum(workingdays) > Only(Daily_Target), Sum(Sales)), Customer))

Hope this helps you.

Regards,

Jagan.

[#4]

thanks jagan