How to create a dynamic expression?

[qliknoob]

Hey guys,

A QlikNoob here. So I've had the pleasure of using Qlik Sense during this last week as my company has chosen to implement it.

The majority of the files we sell to our customers are simple data files. They are very simple to complete in Excel but it is very difficult to replicate these in Qlik. A big issue I have come across is the fact that I am unable to recreate a table that is already on Excel.

Currently, I am struggling with creating a table where it shows as follows; Region, Country, Yearly data, Quarterly Data and Monthly DAta. I have also attached a screenshot to show what I would like the output to be.

I have tried to do this with a pivot table but, the way our data is structured, when I combine the months with the years (i.e. Jan-2016) the pivot table simply drills up, so the only way to view the months would be to drill down for each year.

I have also tried using a standard table but have struggled as when I add years, it simply creates a column for the years and then a column for the volumes. A work around for this would be to use Set Analysis to create a Measure with the following expression =Sum({<Year = {2024}>}[Volume]). This allows me to pivot the data for the year 2024. I have had to create a new Measure for each year from 2016 to 2026. Now, if I do this for the rest of the file I havfe calculated that I will have to make 127 columns to replicate the layout of the original excel file. With this in mind, would there be any dynamic way to extract; firstly the years, then the years with quarters and then the years with months??

Sorry for the long post as I just wanted to give people the context.

I'm looking forward to being a big part of the community.

[marcus_sommer]

Your challenge shouldn't be addressed in the UI else with a more suitable datamodel. This is quite easy to get by transforming the data with The-Crosstable-Load and if there are really monthly data for all quarters and years you should only load the monthly data because you could convert the months in a following step into a date and associate it with a master-calendar.

After this you could use one simple expression: sum(Volume) and apply those dimensions into pivot you want.

- Marcus

[qliknoob]

Hi Marcus,

Thank you for this! It definitely Qliked (pardon the pun) when you said that I don't actually need the yearly or quarterly data.

An important thing I should've mentioned was that our data is sat in SQL. So any data I need is extracted from our SQL tables and the file shown in the screenshot is from a join of these tables.

In normal circumstances, I would run the template we had created in excel and this would run a stored procedure in SQL and place the data in the Excel spreadsheet. I would then send the file to the client. As we have now implemented Qlik, the company would like to automate this whole process so that every month (when the latest issue of our data is available) the table in Qlik is updated with the latest data so that there is no need for me to run the template and send the file to the client.

In this case I do not believe Cross-Table would work as the data is already displayed vertically in our SQL tables.

Come to think of it, I don't believe I can only have monthly data as it still needs a year column to denote what year that monthly data is for. I have attached a screenshot to show how the data is structured in our SQL databases.

The issue is that, again, I am unable to exactly replicate the the screenshot of the Excel file shown in the initial post.  I am unable to display the data as Region, Country,  Volumes By Year, Volumes By Quarters and Volumes By Month.

Thanks again for the reply!

[marcus_sommer]

If your first screenshot should be just the output from quite "normally" structured database-data - it seems quite simple. Loading the data on a date-level (if there are only year and month it could be merged to a real date), then associating the data with a master-calendar and then you could use a table-chart with multiple expressions which return all your month/quarter/year results (with set analysis you could make them dependent to max. date).

Much more easier in the UI (but with more efforts in the script) would be to add an as-of-table for all your periods.

- Marcus

[qliknoob]

Hi Marcus,

Thank you for the reply again.

So I have just completed creating the master calendar. As well as this, I have also Loaded the data on a date level and then connected this to a Master-Calender where the key is the full date.

Now, moving onto your suggestion for multiple expression is where I am rather confused. From what I understand from your suggestion, would I need to write an expression for each column (i.e. Volumes By Year, Volumes By Quarter, Volumes By Month for the years 2016 through to 2026)?? 

I don't believe the As-Of Table will work as I am trying to avoid creating a separate expression for each column.

Would it be a good option to convert the dimension values (in the Years, Month and Quarter dimensions) into an array and store in a variable, like such?? Would this allow me to drag and drop the required dimensions into a straight table?

Your help isn't going unnoticed, Marcus. Thank you very much!

[marcus_sommer · Accepted solution]

Yes, my first suggestion meant to create multiple different expressions like:

sum({< MonthNum = {"$(=max(Month))"}>} Value)
sum({< MonthNum = {"$(=max(Month)-1)"}>} Value)

Usually you need just a few of them, for example to compare one period against another (by exceeding years in regard to months/quarters a continuing period-field like year * 12 + month would simplify this task).

If you want to output a large number of such period-values it will be a bit tedious but it's still possible (and expressions could be copied - and then just the set analysis filter needs to be adjusted). Your idea with an array within a variable which is then used as a calculated dimension is quite probably possible - but IMO it's much more complicated as sensible especially compared against the above mentioned simple copy + paste approach.

Much easier would be the before mentioned The-As-Of-Table which would just contain multiple redundant dates but each time with a different period-value, for example today would be loaded thrice - once for the year 2019 and once for the month Sep 2019 and once for the Q3 2019 - maybe as a field [PeriodMixmatch]. And this then for all your dates. And after that you could use [PeriodMixmatch] as horizontal dimension in a pivot and just sum(Value) to return all your wanted period-fields. It sounds more complicated as it is - just try it.

- Marcus