Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression Issue

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

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Wow - big expression...!

I've not looked at it in detail but the way I would work with something of this size is to put all the component parts into separate variables and see if each of them is returning the right value on its own. Then build the final expression using the evaluated variables.  Helps to see where it might be going wrong - in a component part or the final construction.

Try that and see how it works out. Using variables also means if you ever need to change the expression you only do it in one place.

Hope this helps,

Jason

View solution in original post

4 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Wow - big expression...!

I've not looked at it in detail but the way I would work with something of this size is to put all the component parts into separate variables and see if each of them is returning the right value on its own. Then build the final expression using the evaluated variables.  Helps to see where it might be going wrong - in a component part or the final construction.

Try that and see how it works out. Using variables also means if you ever need to change the expression you only do it in one place.

Hope this helps,

Jason

Not applicable
Author

Jason,

Yeah I was thinking I could do that after I posted. This is just one of the examples that I find it a pain in trying to transfer crystal logic over to Qlikview, this and trying to convert running totals/crystal variable logic.

I will try breaking it apart into the seperate variables to see if that works.

Not applicable
Author

Separating them is what helped. It allowed me to see that when I separated my expression for Prior, that I had the parenthesis wrong.

(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)

)))

This is the correct expression. The total expression is now at least giving me a result, even if it is the wrong result. I at least have something to work with now.

Thank you.

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Coolio - glad it helped.