Calculating only certain rows when using variables

[JohannesKostaja]

Hi!

I'm creting new Qlik Sense versions of our old QlikView apps. One of the positive side effects of this migration is the possibility to reduce the the horrifying amount of variables we have in the application. I was wondering, whether it is possible to use set expressions or some other means to handle this particular problem I've encountered

Currently in QlikView, all the expressions in the above Pivot table have their own variables, so there is a total of 17 of them (Arpu, Pcs and Revenue * 5 Sales Types + 2 net measures) in this object. Intuitively I would think that 3 or 5 variables would suffice, if there was a possibility to calculate them using set expressions. However, set expressions don't seem to be usable here(?) since the measures are just "=$(Variable)".

Another way to do this could be bu using Sales Type as a dimension. However, the net measures cause trouble here. Does anyone have an idea on how to tackle this easily?  Making 17 variables is naturally an option here as it was with QlikView. 

Thanks!

Ps. Pardon me if there is some very simple way to tackle this issue, Googling only gave me results related to using set analysis within variable definition, which is what I'll do in case there is no way to do this with less variables.

[Carlos_Reyes · Accepted solution]

So... the next lines are my thoughts after trying to guess what's the actual scenario you're facing from a single picture... perhaps there are other possibilities but you'd need to provide a sample app in order for us to better help you.

It really depends on your data model and the nature of the measures. Just from your description I get the idea that some of the rows/expressions are a Sum of units while other rows (Net Sales or Net revenue) are the sum of amounts... also, the fact you mention there are 17 different variables/expressions makes me think you're probably using pick() and match() functions to get the right formula/variable for the right row of measures?

If my thoughts are correct... I don't think there is much else you can do but replicate the same thing in sense, that is if you strictly want to recreate the same exact object and you don't want to mess with your data model. 

In my humble opinion, for you to be able to use set analysis here to simplify the number of expressions/variables... you'd need to add flags and tables in your data model that give you a way to use one single expression for all your measures and the difference would be which records relate to each one of the title of the measures through the new flags/tables that control the relationships... which sounds really confusing and like a lot of work... and it is!

If your formulas are very different between each other, I don't think it's worth trying what I described in the previous paragraph. Specially if you're reading/updating your variables from an excel or other centralized file/location that helps you to manage them easily.

[JohannesKostaja]

Well I've already spent quite some time with the data model so no more changes for that 😄 Thanks for the Excel-tip, I'll start managing the variables in some planned and centralized manner!