# Expression Issue

**Marc Livingston**Sep 15, 2011 4:13 PM

If anyone else here has tried converting a heavy formula crystal report, they know it can be a pain.

I have a formula in crystal that access's other formulas to get data. With qlikview I had to combine all formulas into one expression, and then also incorporate set analysis to get around some other issues with possible selections vs crystal's parameter options.

The result is the folowing formula. However this expression is not returning any data. The pivot that it resides in just shows blank.

The expression is complicated enough, and does not show any errors so I have no idea what is going on.

Can anyone with more experience help me out?

if( left(glaccount,1)='1', //{@current} (sum({$< [Fiscal Period] = {"<=$(=Max([Fiscal Period]))"}>} if([Category Description]<>'Cash & Equivalents',(if( [Fiscal Period]=0 , 0 , creditamt+debitamt) + openbalance))*-1)) //end current + //{@Prior} (if (Max([Fiscal Period])-Min([Fiscal Period])<>1, (sum({$< [Fiscal Period] = {"<$(=Max([Fiscal Period]))"}>}if([Category Description]<>'Cash & Equivalents', if ([Fiscal Period]=0 , 0 , (creditamt+debitamt) + openbalance), sum({$< [Fiscal Period] = {"<=$(=Max([Fiscal Period]))"}>}if([Category Description]<>'Cash & Equivalents', if ([Fiscal Period]=0 , 0 , (creditamt+debitamt) + openbalance),0) )))))) //end prior , //Current (sum({$< [Fiscal Period] = {"<=$(=Max([Fiscal Period]))"}>} if([Category Description]<>'Cash & Equivalents',(if( [Fiscal Period]=0 , 0 , creditamt+debitamt) + openbalance))*-1)) //end current - //Prior (if (Max([Fiscal Period])-Min([Fiscal Period])<>1, (sum({$< [Fiscal Period] = {"<$(=Max([Fiscal Period]))"}>}if([Category Description]<>'Cash & Equivalents', if ([Fiscal Period]=0 , 0 , (creditamt+debitamt) + openbalance), sum({$< [Fiscal Period] = {"<=$(=Max([Fiscal Period]))"}>}if([Category Description]<>'Cash & Equivalents',if([Fiscal Period]=0 , 0 , (creditamt+debitamt) + openbalance),0) )))))) //end prior )

Edit: Here are the Crystal formulas if that helps at all:

Main Formula:

if {@Group 1 name}="Assets" then ((Sum ({@current}, {coaactcat.number01})*-1) + Sum ({@Prior}, {coaactcat.number01})) else

(Sum ({@current}, {coaactcat.number01})*-1)-(Sum ({@Prior}, {coaactcat.number01})*-1)

@current:

if {glperiodbal.fiscalperiod}<={?Fiscal Period to}and {coaactcat.description}<>"Cash & Equivalents" then {@Combined} else 0

@prior:

if {?Fiscal Period to}-{?Fiscal Period From:}<>1 then

(if {glperiodbal.fiscalperiod}<({?Fiscal Period from:})and {coaactcat.description}<>"Cash & Equivalents" then {@Combined 3} else 0)

else(if {glperiodbal.fiscalperiod}<=({?Fiscal Period from:})and {coaactcat.description}<>"Cash & Equivalents" then {@Combined 3} else 0)

@combined:

(if {glperiodbal.fiscalperiod}=0 then 0 else {glperiodbal.creditamt}+{glperiodbal.debitamt}) + {glperiodbal.openbalance}

@combined3:

(if {glperiodbal.fiscalperiod}=0 then 0 else {glperiodbal.creditamt}+{glperiodbal.debitamt}) + {glperiodbal.openbalance}

{?Fiscal Period to}: Min fiscal period selected

{?Fiscal Period from:}: Max fiscal period selected