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: 
MrAndrews
Contributor III
Contributor III

Strange behaviour with Set Analysis with Variables and Ifs!

Hi

I've run into some trouble with a variable, with parameters, and I'm not sure how to overcome it!

My Variable vProdCost  is called like this  $(vProdCost(Actual,"0","L1","*"))

and looks like this:

IF('$1'='Actual',

                IF(AsOfPeriod > $(vProdCutOff),0,Num(sum({<Version={$1},MonthDiff={$2}, CostLevel={$3},HierarchyLevel={$4}>} CostAmount ),vProdFormatMoney))

    ,Num(sum({<Version={$(vProdComp)},MonthDiff={$2},CostLevel={$3},HierarchyLevel={$4}>}CostAmount),vProdFormatMoney)

)

This works fine, but I also need to add another criteria into the if statement.. When I add it in I start to get All the other CostLevels with 0's in, and I only want to see L1s (is in $3).  This can be seen in the attachments

The new statement is like this:

IF('$1'='Actual' AND  (AsOfPeriod <= $(vProdCutOff)),

                IF(AsOfPeriod > $(vProdCutOff),0,Num(sum({<Version={$1},MonthDiff={$2}, CostLevel={$3},HierarchyLevel={$4}>} CostAmount ),vProdFormatMoney))

    ,Num(sum({<Version={$(vProdComp)},MonthDiff={$2},CostLevel={$3},HierarchyLevel={$4}>}CostAmount),vProdFormatMoney)

)

I've tried loads of things to try and get around it - different nestings etc, but no luck! Any ideas?

Cheers

 

 

 

 

2 Replies
MrAndrews
Contributor III
Contributor III
Author

so, it's not  when a second constraint is added - it is when I try to constrain based on a non parm field... is that how it works? Does it not do it row by row?  I can't refer to information outside of the calc?

MrAndrews
Contributor III
Contributor III
Author

i'm a divvy - added into the set analysis.... you can look into things for too long!