Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Help - Qlik Sense expressions containing strings and variables

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

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

4 Replies
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
Not applicable
Author

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.)

Gender Diversity.PNG

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

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
Not applicable
Author

Hi Marcus,

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

Thanks very kindly,

Ozzie