Percent column gives blank

[former_member580112]

Hello


I have two groups 

CurrMonth          YTD        %Value
  A1               10           
  A2               40           
  A3               60
Budget
  AB1              40
  AB2              30
  AB3              50

Now I would like to calculate the %value which is CurrMonth
A1/ Budge AB1 ((10/40)*100)

I have setup the two #RT – 
1. CurrMonYTD – Sum and set formula
   command.currmonth in [A1,A2,A3]

2.  BudgetYTD – Sum and set formula command.Budget in [AB1,AB2,AB3]

Then Set a formula @percent

({# CurrMonYTD}/{# BudgetYTD })*100

When I place this formula on my report it just gives me blank.

Any idea/ Help?

Thanks


 


 

[former_member580112]

Can you please help on this?

[former_member580112]

Can you please help on this?

Thanks.

[DellSC]

Running totals won't work for this. How many lines (A1, A2, etc) do you have? Do all of the A lines have a corresponding B line?

-Dell

[former_member580112]

Hello.

Thank you for your reply. I have 4 different departments in current month group and the same departments have in Budget group.

Before YTD I have months column from January to December and then YTD. The YTD value is calculated the sum of January to December

Last column is "% values", which should be ("CurrentMonth YTD"/ "Budget YTD")*100.

The reason, I set up the running total, so I have an option in "Evaluate" section, where I can put the formula to sum only the "CurrentMonth" group. If RT will not work here then how do I do that part by setting the formula?

I tried setting up the formula as follow,

If {Command.CurrentMonth} in ['A1','A2','A3','A4']

Then

SUM({YTD}) // in this case it should only sum of the "CurrentMonth" values.

but somehow it's not giving me the right values, instead it gives me blank.

Please help.

Thanks

[former_member580112]

Hello Again,

I think I found what was wrong with my formula and fix it. Now I have two formula setup one for CurrentMonth Group and one for Budget Group

//Current Month Group Formula

@YTDCurrMon

If {Command.CurrentMonth} in ['A1','A2','A3','A4']

Then

Sum (Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec)

//Budget Group Formula

@YTDBudget

If {Command.Budget} in ['AB1','AB2','AB3','AB4']

Then

Sum (Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec)

Now Just to check whether it gives me correct value or not

place CurrentMonth group formula (@YTDCurrMon) under the "% value" then I can get correct YTD values for the current Month and get "0" for the Budget group.

If I place Budget group formula (@YTDBudget) under the "% value" then I can get correct YTD values for the Budget Group and get "0" for the CurrentMounth group.

Then I created a new formula called

@percentvalue

If {Command.CurrentMonth} in ['A1','A2','A3','A4']

THEN

({@YTDCurrMon}/{@YTDBudget})*100

This formula compiled without any issue. But when I placed under the "% Value" column I got the error message "Division by Zero".

Please help how do I fix this?

Thank you.

[former_member580112]

Can you please help on this?

Thanks.

[DellSC]

You'll need to change your If statement to prevent the division by 0 error. Also, Crystal has a percentage operator that will automatically do the "*100". So, your formula would look like this:

@percentvalue

If {Command.CurrentMonth} in ['A1','A2','A3','A4'] and {@YTDBudget} > 0 THEN
({@YTDCurrMon}%{@YTDBudget})

-Dell

[former_member580112]

Hello,

Thanks for your reply. I tried adding both values >0 so my formula looks as follow

@percentvalue

If {Command.CurrentMonth} in ['A1','A2','A3','A4'] and {@YTDCurrMon} > 0 and {@YTDBudget} > 0

THEN

({@YTDCurrMon}%{@YTDBudget})

But still I'm getting "Division by Zero" message.. So @YTDCurrMon and @YTDBudget gives correct value for that group. So what I mean is, if I place @YTDCurrMon in %value column it returns as follow

CurrMonth          YTD        %Value
  A1               10           10
  A2               40           40
  A3               60           60  
Budget
  AB1              40            0
  AB2              30            0
  AB3              50            0

If I place @YTDBudget in %value column, it returns as follow

CurrMonth          YTD        %Value
  A1               10           0
  A2               40           0
  A3               60           0  
Budget
  AB1              40            40
  AB2              30            30
  AB3              50            50

So when i calculate percent as follow in percent formula

If {Command.CurrentMonth} in ['A1','A2','A3','A4'] and {@YTDCurrMon} > 0 and {@YTDBudget} > 0

THEN

({@YTDCurrMon}%{@YTDBudget})

It is still picking that Zero(0) value and give me "Division by Zero" error. So how do I avoid this?

Please help.

Thank you again.

[former_member580112]

I also tried

If {Command.CurrentMonth} in ['A1','A2','A3','A4'] and {@YTDCurrMon} > 0 OR {@YTDBudget} > 0

THEN

({@YTDCurrMon}%{@YTDBudget})

But still gives me same error.

Thanks.

[DellSC]

"Divide by 0" only applies when the denominator ( the number after "/") is 0. If the numerator is 0, then the result will be 0.

I think I know what the issue is, though. Edit your other formulas ({@YTDCurrMon}, {@YTDBudget}, etc). At the top middl of the formula editor, change "Exceptions for nulls" to "Default values for nulls". This will automatically replace null values with zeros so that you don't have to code for them. Null works different from 0. If, for example, {@YTDBudget} is returning null instead of 0, looking for {@YTDBudget} > 0 will return null, which is neither true nor false. If {@YTDCurrMon} returns null, then you won't have any value show up on the report instead of it showing 0. So, having null numbers automatically replaced with 0 will alleviate both of these issues.

-Dell

BTW, null is a database thing, not a Crystal thing - Crystal just uses what's coming from the database.

[former_member580112]

Good Morning!

Thanks for your reply and explanation. Per your advice have changed "Exceptions for nulls" to "Default values for nulls" for all these three formulas,

{@YTDCurrMon}

{@YTDBudget}

{@percentvalue}

when I place "@percentvalue" on the report I do not see "Division by Zero" error but it shows "0" instead of the actual percent value for those rows.

Did I miss anything from your instruction?

Please help.

Thanks again,

[DellSC]

No, that's exactly what it should show - if any of the data is null, the result of the calculation is 0.

Also, you can format the numbers to include the % sign by right-clicking on the formula in your report and selecting "Format Field...". On the Number tab, click on the "Customize" button. Go to the "Currency Symbol" tab and check Enable Currency Symbol. Set the position so that the symbol is on the right and set the symbol to "%". Click OK twice.

-Dell

[former_member580112]

Hello,

Thanks for your quick reply and detail steps. By following that I changed the column format to include "%" at the end of the each value. But still it gives me "0.0%" for each values.

So not sure what is still missing or what needs to be changed to get the correct values.

Please help.

Thank you.

[DellSC]

To debug this, put the {@YTDCurrMon} and {@YTDBudget} formulas on the report instead of the percentage. One or both of those will probably give a value of 0. From there, you need to determine what's wrong with the formula(s) that they're calculating 0 instead of a number.

-Dell

[former_member580112]

Hello Again,

So I changed the formula as follow,

If {Command.ReportGroup} in ['A','B','C','D']

AND {@YTDCurrMon} > 0

OR {@YTDBudgt} > 0

THEN

{@YTDCurrMon} % {@YTDBudgt}

still kept the setting "Exceptions for nulls" to "Default values for nulls" , When i save this formula I got a "Division by Zero" message again.

Please help.

Thanks,

[former_member580112]

I did try that couple days back by putting {@YTDCurrMon} and {@YTDBudget} formulas on the report, I got following.,

CurrMonth          YTD        %Value
  A1               10           10
  A2               40           40
  A3               60           60  
Budget
  AB1              40            0
  AB2              30            0
  AB3              50            0

If I place @YTDBudget in %value column, it returns as follow

CurrMonth          YTD        %Value
  A1               10           0
  A2               40           0
  A3               60           0  
Budget
  AB1              40            40
  AB2              30            30
  AB3              50            50


so as you can see from following that those formulas works fine. so not sure why the percent formula does not return correct value?
Thanks.

[DellSC]

Because of the way ands and ors work, your formula is wrong - it needs some parentheses:

If {Command.ReportGroup} in ['A','B','C','D']
AND ({@YTDCurrMon} > 0 OR {@YTDBudgt} > 0)
THEN
{@YTDCurrMon} % {@YTDBudgt}

If you don't include the parentheses, you'll get all records where {@YTDBudgt} is greater than 0, not just the ones from the correct report groups.

Please post your {@YTDCurrMon} and {@YTDBudgt} formulas so I can take a look at them.

Thanks!

-Dell

[DellSC]

Also, try putting the two formulas next to each other on the report instead of using one or the other. I suspect that you're getting 0 values because in the CurrMonth section you don't have values for {@YTDBudgt} yet and in the Budget section you don't have values for {@YTDCurrMon} because you've gone past those records.

-Dell

[former_member580112]

Hello,

Thanks for your quick reply. I tried putting parenthesis surrounding ANDs and OR. But still got the same error message. Here are formulas.

// YTD Caclulation: This formula will calculate the YTD values.

//Define Variables and set to 0 if value is Null
NumberVar Jan;
Jan:=If ISNULL ({Command.January}) Then 0 Else {Command.January};
NumberVar Feb;
Feb:=If ISNULL ({Command.February}) Then 0 Else {Command.February};
NumberVar Mar;
Mar:=If ISNULL ({Command.March}) Then 0 Else {Command.March};
NumberVar Apr;
Apr:=If ISNULL ({Command.April}) Then 0 Else {Command.April};
NumberVar May;
May:=If ISNULL ({Command.May}) Then 0 Else {Command.May};
NumberVar Jun;
Jun:=If ISNULL ({Command.June}) Then 0 Else {Command.June};
NumberVar Jul;
Jul:=If ISNULL ({Command.July}) Then 0 Else {Command.July};
NumberVar Aug;
Aug:=If ISNULL ({Command.August}) Then 0 Else {Command.August};
NumberVar Sep;
Sep:=If ISNULL ({Command.September}) Then 0 Else {Command.September};
NumberVar Oct;
Oct:=If ISNULL ({Command.October}) Then 0 Else {Command.October};
NumberVar Nov;
Nov:=If ISNULL ({Command.November}) Then 0 Else {Command.November};
NumberVar Dec;
Dec:=If ISNULL ({Command.December}) Then 0 Else {Command.December};
// Calculate Actual amouth of YTD
If Month({?MonthEndDate}) = 1 Then
Jan
Else If Month({?MonthEndDate}) = 2 Then
Jan+Feb
Else If Month({?MonthEndDate}) = 3 Then
Jan+Feb+Mar
Else If Month({?MonthEndDate}) = 4 Then
Jan+Feb+Mar+Apr
Else If Month({?MonthEndDate}) = 5 Then
Jan+Feb+Mar+Apr+May
Else If Month({?MonthEndDate}) = 6 Then
Jan+Feb+Mar+Apr+May+Jun
Else If Month({?MonthEndDate}) = 7 Then
Jan+Feb+Mar+Apr+May+Jun+Jul
Else If Month({?MonthEndDate}) = 8 Then
Jan+Feb+Mar+Apr+May+Jun+Jul+Aug
Else If Month({?MonthEndDate}) = 9 Then
Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep
Else If Month({?MonthEndDate}) = 10 Then
Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct
Else If Month({?MonthEndDate}) = 11 Then
Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov
Else If Month({?MonthEndDate}) = 12 Then
Jan+Feb+Mar+Apr+May+Jun+Jul+Aug+Sep+Oct+Nov+Dec

// YTDCurrMon

If {Command.ReportGroup} in ['Motorsports-CurrMonth','Eng Svcs-CurrMonth','Production-CurrMonth','Other-CurrMonth'] Then {@YTD Calculation}

// YTDBudget

If {Command.ReportGroup} in ['Motorsports-Budget','Eng Svcs-Budget','Production-Budget','Other-Budget'] Then {@YTD Calculation}

If I put both formulas side by side I get following.

CurrMonth     %Value   %Value
  A1           10         0
  A2           40         0
  A3           60         0  
Budget
  AB1           0         40 
  AB2           0         30
  AB3           0         50 


Can you please let me know what is wrong in above formulas?

Thank you.

[DellSC]

Because the values you're looking for appear to be in multiple records, you're not going to be able to get the budget values for doing the percentage with the current month values because the records with the budget data are processed after the records with the current month data.

If you'll post the command that you're using, I may be able to re-write it for you so that you can get what you're looking for.

-Dell

[former_member580112]

Hello,

Thanks for your reply. Following commands I have used in my report

{Command.January}
{Command.February}
{Command.March}
{Command.April}
{Command.May}
{Command.June}
{Command.July}
{Command.August}
{Command.September}
{Command.October}
{Command.November}
{Command.December}
{Command.DeptNam}

As you know that i'm trying to calculate the % value for current Month.

Thanks again.

[DellSC]

What is the SQL in the queries? Are you working on a calendar year (Jan - Dec) basis? Would you also please post a screen shot of the Linking tab of the Database Expert for your report?

Thanks!

-Dell

[former_member580112]

Thanks for your quick reply. Here is the sql query I use to put data for this report. Yes we are working on a calendar year. Since I'm using only one query I do not have any thing to link here.

WITH 
mends as (
SELECT DateID as MonthEnd
FROM t.[Date] 
WHERE DateID >= '2017-01-01' AND IsLastDayOfMonth = 'Y' 
),
actuals as (
SELECT
mends.MonthEnd,
MONTH(p.PostingDateID) as MonthNum
,a.AccountGroupDescr
,CASE BusinessUnitCode
WHEN '2' THEN 'Motorsports'
WHEN '3' THEN 'Eng Svcs'
WHEN '5' THEN 'Production'
ELSE 'Other'
END as ReportGroup
,SUM(
CASE WHEN p.PostingDateID BETWEEN DATEADD(dd, 1, EOMONTH(mends.MonthEnd, -1)) AND mends.MonthEnd THEN
CASE AccountCategoryDescr 
WHEN 'Assets' THEN DebitAmount
WHEN 'Cost of Sales' THEN DebitAmount
WHEN 'Equity' THEN -1 * DebitAmount
WHEN 'Income Tax' THEN DebitAmount
WHEN 'Liabilities' THEN -1 * DebitAmount
WHEN 'Non Financials' THEN 0
WHEN 'Other Income/Expense' THEN DebitAmount
WHEN 'Revenue' THEN -1 * DebitAmount
WHEN 'Expenses' THEN DebitAmount
END
END
)
+
SUM(
CASE WHEN p.PostingDateID BETWEEN DATEADD(dd, 1, EOMONTH(mends.MonthEnd, -1)) AND mends.MonthEnd THEN
CASE AccountCategoryDescr 
WHEN 'Assets' THEN -1 * CreditAmount
WHEN 'Cost of Sales' THEN -1 * CreditAmount
WHEN 'Equity' THEN CreditAmount
WHEN 'Income Tax' THEN -1 * CreditAmount
WHEN 'Liabilities' THEN CreditAmount
WHEN 'Non Financials' THEN 0
WHEN 'Other Income/Expense' THEN -1 * CreditAmount
WHEN 'Revenue' THEN CreditAmount
WHEN 'Expenses' THEN -1 * CreditAmount
END
END
) as CurrentMonth
FROM mends
CROSS APPLY t.GLAccount as a
LEFT JOIN t.GLPostingSummary as p
ON a.GLAccountID = p.GLAccountID
WHERE
a.AccountCategoryDescr IN ('Reve')
GROUP BY 
mends.MonthEnd,
MONTH(p.PostingDateID) 
,a.AccountGroupDescr
,CASE BusinessUnitCode
WHEN '2' THEN 'Motorsports'
WHEN '3' THEN 'Eng Svcs'
WHEN '5' THEN 'Production'
ELSE 'Other'
END 
),
budget as (
SELECT
MONTH(b.PeriodEndDateID) as MonthNum
,b.PeriodEndDateID as MonthEnd
,g.BusinessUnitCode
,g.ReportGroup
,b.BudgetAmount
,SUM(b.BudgetAmount) OVER (PARTITION BY YEAR(b.PeriodEndDateID), g.BusinessUnitCode, g.ReportGroup) as FullYearBudget
FROM acctGroups as g 
LEFT JOIN t.GLBudgetSnapshot as b
ON b.ReportGroup = g.ReportGroup
AND b.DeptCode = g.DeptCode
WHERE g.AccountCategoryDescr = 'Revenue'
), 
pivotedcurrmonth as ( 
SELECT 
pvt.MonthEnd,
pvt.ReportGroup + '-CurrMonth' as ReportGroup,
pvt.[1]/1000 as January, 
pvt.[2]/1000 as February,
pvt.[3]/1000 as March,
pvt.[4]/1000 as April,
pvt.[5]/1000 as May, 
pvt.[6]/1000 as June,
pvt.[7]/1000 as July,
pvt.[8]/1000 as August,
pvt.[9]/1000 as September,
pvt.[10]/1000as October,
pvt.[11]/1000 as November,
pvt.[12]/1000 as December,
    0.0/1000 as PlcHld
FROM
( 
SELECT
ReportGroup
,MonthNum
,mends.MonthEnd
,SUM(CurrentMonth) as CurrentMonth
FROM mends
LEFT JOIN actuals as a
ON YEAR(a.MonthEnd) = YEAR(mends.MonthEnd)
GROUP BY
ReportGroup
,MonthNum
,mends.MonthEnd
) as p 
PIVOT 
(
SUM(CurrentMonth)
FOR MonthNum IN
([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) as pvt 
),
pivotedBudget as ( 
SELECT 
pvt.MonthEnd,
pvt.BusUnit + '-Budget' as BusUnit,
pvt.[1]/1000 as January, 
pvt.[2]/1000 as February,
pvt.[3]/1000 as March,
pvt.[4]/1000 as April,
pvt.[5]/1000 as May, 
pvt.[6]/1000 as June,
pvt.[7]/1000 as July,
pvt.[8]/1000 as August,
pvt.[9]/1000 as September,
pvt.[10]/1000 as October,
pvt.[11]/1000 as November,
pvt.[12]/1000 as December,
(pvt.FullYearBudget)/1000 as FullYearBudget
FROM 
( 
SELECT
CASE BusinessUnitCode
WHEN '2' THEN 'Motorsports'
WHEN '3' THEN 'Eng Svcs'
WHEN '5' THEN 'Production' 
ELSE 'Other'
END as BusUnit
,MonthNum
,mends.MonthEnd
,SUM(BudgetAmount) as BudgetForMonth
,MAX(FullYearBudget) as FullYearBudget
FROM mends
LEFT JOIN budget as b
ON YEAR(b.MonthEnd) = YEAR(mends.MonthEnd)
GROUP BY
CASE BusinessUnitCode
WHEN '2' THEN 'Motorsports'
WHEN '3' THEN 'Eng Svcs'
WHEN '5' THEN 'Production' 
ELSE 'Other'
END
,MonthNum
,mends.MonthEnd
) as p 
PIVOT

(
SUM(BudgetForMonth)
FOR MonthNum IN
([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) as pvt
)
select * from 
(SELECT * 
FROM pivotedcurrmonth
WHERE MonthEnd ={?MonthEndDate}
UNION ALL
select * from pivotedBudget
where MonthEnd={?MonthEndDate}) m
order by CASE m.ReportGroup
WHEN 'Motorsports-CurrMonth' Then 1
WHEN 'Eng Svcs-CurrMonth' Then 2
WHEN 'Production-CurrMonth' Then 3
WHEN 'Other-CurrMonth' Then 4
When 'Motorsports-Budget' Then 5
When 'Eng Svcs-Budget' Then 6
When 'Production-Budget' Then 7
When 'Other-Budget' Then 8
End
;



Thanks again.

[former_member580112]

Can you please help on this?

Thanks

[DellSC]

Sorry, I've been tied up with work stuff. The query needs to be re-written so that you have both the current and the budget numbers on the same row. I've got a start on it, but it will probably be tomorrow before I can get something to you.

-Dell

[former_member580112]

Thank you for your reply. Not a problem. Your help is greatly appreciated.

will wait to hear from you on this..

Thanks again,

[former_member580112]

Just checking to see whether you got a chance to look into this? If so any help would be appreciated.

Thanks.

[DellSC]

I'm looking at it now and I have a question:

For the budget numbers, are they just annual values or is there a different value for every month?

Thanks!

-Dell

[former_member580112]

Hello,

Thank you very much for your reply. So the way we have setup here is we have monthly budget and and then we have yearly budget.

Hope this answer your questions.

Thanks again.

[DellSC]

So, are you comparing the monthly actual to the monthly budget and also the ytd actual with the ytd budget?

Thanks!

[former_member580112]

Thanks for your reply. Yes that the way we compare the values.

09/27/2018

Just checking to see whether you got a chance to look into this? If so any help would be appreciated.

Thanks.