Help - Qlik Sense expressions containing strings and variables

[Anonymous]

Hi,

I'm attempting to represent the total using a combination of text, a dimension and a variable in a subtitle expressions as follows:

'Total: ' & if(Title = 'Total Members', $(CurrPeriod),0)

where Title is a dimension and CurrPeriod is a variable that is set using Let in the load scripts.

I've spent some time using Google to find a suitable answer and tried many different suggestions but it is still not working.

Any help getting this right will be appreciated.

Thanks kindly,

Ozzie

[marcus_sommer]

Quite probably will Title return NULL because there is no aggregation on them and I think there will be more than one possible value for this dimension. Also for your variable I'm not sure if the expected result will be returned.

How looked the statement for the variable and which result is expected? Further a screenshot with your object will be helpful.

- Marcus

[Anonymous]

Hi  Marcus,

I appreciate your response to my post.  Below are some further details as requested.

The subtitle line in the following chart shows the expression in question (Total: 8963 etc.)

In the above chart the Subtitle string is created using the following expression:

'Total: ' & sum(if(Title = 'Total Members', [Nov],0)) & ' {' &

'Female: ' & sum(if(Title = 'Females', [Nov],0)) & ',   Male: ' & sum(if(Title = 'Males', [Nov],0)) &'}'

The above expression works fine but it is one of many such expressions in my executive overview app where the month dimension is “hard-coded”.  Every time the reporting period changes I have to find each of the 20 or so expressions and change them. Eg. [Oct] to [Nov] etc.  Tedious to maintain.

I want to create a single point of maintenance in the load scripts using variables for re-use in the app whenever the reporting period changes rather than have to do many changes mentioned above.

So after some research and reading in our Qlik forums, I created 2 Let statements at the bottom of the following load script:

[Org Health Summary - P&C]:

LOAD

[DataSource],

[Category],

[Sub-Category],

[Title],

[Prev FY-1],

[Prev FY],

[Jul],

[Aug],

[Sep],

[Oct],

[Nov],

[Dec],

[Jan],

[Feb],

[Mar],

[Apr],

[May],

[Jun]

FROM [lib://Data/Our People.xlsx]

(ooxml, embedded labels, header is 1 lines, table is [Org Health Summary - P&C]);

Let CurrPeriod = 'sum(Nov)';

Let PrevPeriod = 'sum(Oct)';

From there, I tried testing the use of one of the variables by changing the first line of the Subtitle script so that it shows:

'Total: ' & if(Title = 'Total Members', $(CurrPeriod),0)

And this did not work. Title is not null and there is a value for the variable (8,963). After all, it did work using [Nov], which is what CurrPeriod is aggregating. But not working when using the variable as an aggregation.

Look forward to hearing from you and others.

Thanks,

Ozzie

[marcus_sommer · Accepted solution]

I suggest that you changed your datamodel by transforming your table per The Crosstable Load into a "normal" tablestructure and afterwards creating a real date from your month-field maybe per something like:

makedate(Year, match(YourMonthField, 'Jan', 'Feb', ...) as Date

and maybe further connecting this date-field with a master-calendar and/or a financial calendar. Here you will find many informations about How to use - Master-Calendar and Date-Values.

In the end you could use expressions like:

sum({< Title = {'Total Members'}, MonthNum = {"$(=max(MonthNum))"}>} YourValueField)

- Marcus

[Anonymous]

Hi Marcus,

Thanks - that is awesome.  I'll work with your suggestions and go from there.

Thanks very kindly,

Ozzie