Variable Help

[dyee4613]

Hello.  I'm trying to figure out the proper syntax for setting a variable.  Here is the code for my set analysis.  

if([P&L] = 'Outside Sales', Sum({$< Object = {">=4020<=4025",">=4160<=4168",">=4320<=4321","4380","4436"},[BU - Code]= {"2000"}>}[Amount]),

if([P&L] = 'Intercompany Sales', Sum({$< Object = {">=4120<=4125",">=4180<=4189",">=4350<=4353","4420",">=4460<=4462"},[BU Code]= {"2000"}>}[Amount]),

This works pretty good for each line-level detail.  However, when I try to roll it up it becomes a bit more problematic.  Ideally, I'd like to convert each line to a variable so when I want to roll them up my formula will be Outside Sales + Intercompany Sales rather than writing the code for all the lines.  

[Lech_Miszkiewicz]

Hi @dyee4613 

I know that in Qlik we can do things in 100 different ways. Important thing is to make it smart. I really dont want to throw you off but I think that building the P&L layout the way you are trying to present is not right approach. 

All this can be handled by proper data modeling where for all account/object/bu code combinations you can create proper grouping table. Such grouping table will allow you to then put [P&L] as a dimension and Sum(Amount)  as Measure and all calculations should be handlend by data model itself. You will not have issues with aggregations, filtering anything.. 

Just use the approach similar to this very old but still applicable how-to document: https://community.qlik.com/t5/QlikView-Documents/How-to-Create-a-Profit-and-Loss-Statement-in-QlikVi... and believe me - once you cross this bridge and get understanding how this can be done it is so much easier. Having set analysis doing what you are doing is possible, but the construction of expression itself creates potential issues: IF (... SUM().....) - all oldschool boys will tell you that this is rather "no, no, no..." approach for the reasons that your measure is not aggregation measure - it is conditional statement.

YOu can consider using Pick(Match() ...) construction of your expression which depending on your actual data model can help, but bottom line is that we should encourage you to learn and do it the best possible way which is by creating grouping dimension and which will make your life much easier later on.

good luck

[dyee4613]

Hi Lech, 

I appreciate your feedback.  To clarify, I'm pretty new to QLIK and I'm doing this to get a better understanding of the writing formulas in QLIK and to provide proof of concept for the team as we evaluate continuing to use our current reporting tool or transition to QLIK.  My goal is to build a report "close enough" to our existing report and mess with the visualization so it looks nicer.  Essentially, I'm trying to get as far as I can without involving IT.

This will not be the actual report in production.  As you mentioned, hardcoding a report like this is a nightmare to manage, especially with this company's insane amount of GL codes.  On top of that, the functionality is a lot worse and later advances to the report (something resembling drilldown) will be impossible with the current setup.  

However, at the moment, I don't really care about that since I'm just trying to build a proof of concept with totals.  Additionally, variables are super useful so it's a great thing to learn but I'm very confused as how to set variables and how to use a variable in a set analysis.

[marcus_sommer]

Just like hinted here: Re: Hierarchy Mapping - Qlik Community - 2457164 Don't try to implement a hard-coded logic - neither in the script nor in the UI - else create appropriate dimension-tables. It's not more work else it will save a lot of efforts.

[dyee4613]

I'm just trying to create a proof of concept and get more familiar with measures and variables.  The plan is likely to modify the ERP to include the correct mapping. 

But, while that's going on, I wanted to build/design something and have the ability to elicit feedback.  

[marcus_sommer]

Especially by starting a project-development without a good knowledge about the data and their quality and/or having a final requirement it's important to keep the things simple and easily adjustable. And the most simple approach is creating an own dimension-table, for example in Excel.

Defining there from-to areas to n hierarchy-level (which are easy to resolve to dedicated values with internal while-loops or an intervalmatch) and/or using Excel expressions like row() + copy & paste and/or fill approaches are very simple and surely much faster as hard-coding them within any variables/expressions.

Further such dimension-table is a centralized place to add and/or change any matching - in opposite to the hard-coding which is needed in n places and n versions. Using (nested) master-items and/or variables to reduce the redundancy is possible but it could increase the complexity quite heavily - and if anything needs to be adjusted it results often in a nightmare because to find and update all places is a very time consuming job.

And the last is nearly mandatory because usually there are in such P&L more as a single company and/or country with more or less different matchings and slightly changes over the year. To include such differences within hard-coded expressions/variables is extremely ugly to impossible. With a dimension-table it's just a copy & paste of it + combining Account & '|' & Year & '|' & Company as KEY and adjusting those records which are different between them.