Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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!