9 Replies Latest reply on Apr 20, 2018 1:43 AM by Anupam Mehta

# Calculation

Hi All,

Here my problem is not working Revenue Calculation here I attached my sample workbook.

This is logic for revenue calculation

how to write below logic in SQL and tableau,

In the SQL, Create new select columns called BlendedRevenue and BlendedRevenueRunrate. The logic for these columns is to pull either expected value (lead revenue)

or revenue based on the product and date. Logic is:

For Personal Loans and Auto Loans:

-- if it is after the 10th of the month, pull expected revenue for the last month and current month, otherwise matchedRevenue

-- if it is before the 10th of the month, pull expected revenue for last 2 months + current month

for all other products, pull matched revenue.

Thanks,

Veeru.

• ###### 1. Re: Calculation

Hi Veeru,

As per your requirement you should be checking the day of month but instead you are checking the month.

to be more precise you have added month(today())>10  instead of day(today()) which is giving incorrect calculations i believe.

Hope this helps.

Thanks,

Bharat

• ###### 2. Re: Calculation

Hi Kumar,

i already checked month(today()) it is also not working.

Thanks,

Veeru.

• ###### 3. Re: Calculation

Veeru,

Ideally You should be adding day(month) in  your calculation but not month(month).

as per your requirement need is to get data based on either  after or before 10th of the month.

Thanks,

Bharat

• ###### 4. Re: Calculation

Hi Veeru,

Check the attached,Hope it clarifies.

Thanks,

Bharat

• ###### 5. Re: Calculation

hi,

Already i tried all the ways here i attached my workbook plz check it

Thanks,

Veeru.

• ###### 6. Re: Calculation

Hi Veeru,

Regards,

Anupam

• ###### 7. Re: Calculation

Hi Anupam,

sum(case [Product] when 'Personal' then

(if month(TODAY()) > 10 then

(IF DATETRUNC('month',[QF Complete Date]) = DATETRUNC('month',TODAY()) then [Expected Lead Revenue]

elseif DATETRUNC('month',[QF Complete Date]) =

elseif month(TODAY()) <= 10 then

(IF DATETRUNC('month',[QF Complete Date]) = DATETRUNC('month',TODAY()) then [Expected Lead Revenue]

elseif DATETRUNC('month',[QF Complete Date]) =

ELSEIF DATETRUNC('month',[QF Complete Date]) =

end)

when 'Automobile' then

(if month(TODAY()) > 10 then

(IF DATETRUNC('month',[QF Complete Date]) = DATETRUNC('month',TODAY()) then [Expected Lead Revenue]

elseif DATETRUNC('month',[QF Complete Date]) =

elseif month(TODAY()) <= 10 then

(IF DATETRUNC('month',[QF Complete Date]) = DATETRUNC('month',TODAY()) then [Expected Lead Revenue]

elseif DATETRUNC('month',[QF Complete Date]) =

ELSEIF DATETRUNC('month',[QF Complete Date]) =

end)

end)

Thanks,

Veeru.

1 of 1 people found this helpful
• ###### 8. Re: Calculation

This one works.

• ###### 9. Re: Calculation

Hello Veeru,

The calculation you have crated is based on month part i.e if month is greater than 10 the perform a calculation else perform B calculation, so whenerver you run report from January to October it will run part A only.

And in problem statement you are looking calculation based on day please find  below

- if it is after the 10th of the month, pull expected revenue for the last month and current month, otherwise matchedRevenue

- if it is before the 10th of the month, pull expected revenue for last 2 months + current month

So it will not work as expected.

Please find the below solution, its returning expected result

CASE [Product]