How can I write a proper expression where my field value can take in multiple element list?

[MalcolmL]

Hi all,

I am very new to Qlik sense and my work requires me to use the visualization tool as part of my scope.

I have few requirements

1)  To show multiple charts (e.g 2 charts) that is independent of each other, which I have done it by simply feeding in data into Qlik via excel and display it on the sheet

2) To create a filter panel with dimension Month that can display the individual chart results for that month ( User can select one or multiple months, for e.g 'Jul' or 'Jul' + 'Aug' ..)

What I have done is that I have created a simple Master Calender via data load editor from today() date -365 rows

I also have created a filter panel that takes in Dimensions Pane Month from the Master Calender

Lastly, I have wrote the expression for the 2 charts above, for example one of them is,  Count({$<[Survey Reply Date.autoCalender.Month] = {"$(=Month)"}>} [Survey Reply Date])

So for the above formula, I would wish to count the total Survey reply Date when the User select a single month/ Combination of months from the filter Panel. The above formula works when user select a single month ('May' for example) but when user select combination of multiple months, it does not show any values.

What I have read up is that I have done a rule-based definition - a search for field values where I have passed in a field and evaluate it's value. I would like to clarify if this is the proper way on how one should be doing (creating master calender and referencing my expression to the master calender etc) and at the same time clarify how can I modify my expression to cater to what I need (Displaying result for single selected months and multiple selected months?

Regards,

Mal

[hic]

If you want the user to select one or several months, then just let them do so in "Survey Reply Date.autoCalender.Month".

The "Count([Survey Reply Date])" will automatically calculate the correct count, without Set Analysis.

[MalcolmL]

Hi Hic,

If I’m not wrong, if I do the above as mentioned by you, it will cause in accuracy result for customer invited ( Survey Invited Dates) as it follows Survey Reply Date autocalender. Which is why in the first place, I created a master calender as I have charts that are suppose to operate independently

[hic]

So you have two dates: "Survey Invited Dates" and "Survey Reply Date". To which of the dates is the master calendar linked? And in which of the dates do you want the user to make the selection?

See also
https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578
https://community.qlik.com/t5/Qlik-Design-Blog/Why-You-sometimes-should-Load-a-Master-Table-several-...

[MalcolmL]

Hi Hic,

By Link, do you mean association? If so, there is no association between the master calender table and the data table that I uploaded to Qlik. Is there a need to establish association between the date field in master calender to one of the date field ( survey invite date or survey reply date)? If there is a need, I don’t see the table in data manager to do any form of association. Not sure whether it got to do with it’s being established via data load editor script

To Answer the later part of your question. I will want to have a control where upon selected by the users for certain months combination, the chart for survey invited date will show the correct number for the selected month. Similar for survey reply dates.

If my filter panel uses survey reply date.autocalender.month as the measurement expression, it will provide accurate result for survey reply date chart but cause inaccuracy of result for the survey invited date chart ( expression used over is Count([Survey invited Date])  as these 2 fields are from the same table

I googled and it appears to me it’s to create a master calender, is this correct?

[hic · Accepted solution]

So, if I understand it correctly, you want the user to select a month (or several) in an unlinked Month field, and then the first KPI object should show the number of invited that month, and the second the number or respondents that month. Correct?

I am not sure it would work, but I would try
Count({<[Survey Invited Date.autoCalender.Month] = Month>} [Survey Invited Date])
Count({<[Survey Reply Date.autoCalender.Month] = Month>} [Survey Reply Date])

I.e. no dollar expansion. Instead a direct field reference.

If that doesn't work, you need to start scripting to create a Canonical Date. See https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

[MalcolmL]

Hi Hic,

This works, thankyou! However, I have some queries in regards to the above formula provided by you vs the formulas that I have been trying using. Sorry replies are not prompt and may differ from your working zone as I am located in southeast Asia and do not have access to my work station all the time for me to test out the expression

1) Count({<[Survey Reply Date.autoCalender.Month] = { $(=Month)} >} [Survey Reply Date])

2) Count({<[Survey Reply Date.autoCalender.Month] = { '$(=Month)'} >} [Survey Reply Date])

3) Count({<[Survey Reply Date.autoCalender.Month] = { "$(=Month)"} >} [Survey Reply Date])

Do correct me if I'm wrong I am using your post as reference https://community.qlik.com/t5/Design/Quotes-in-Set-Analysis/ba-p/1471824. From what I understand,

My 1st question is,

For the second expression, it will evaluate the Month field and treat it as a text before evaluating the field in the set modifier. The third expression, it will treat it as a string.

What about the first one ( I think this equation should be the most correct out of the 3 as single quotes treats the value as text and is case sensitive, double quotes treat the value as string and is case insensitive and with multiple months selected, it is not the right condition)

My second question is,

Upon selecting a single month in the master calender filter panel, in the debugging panel located below within the edit expression view, using expression 1,2,3 evaluates to 

1) Count({<[Survey Reply Date.autoCalender.Month] = { May} >} [Survey Reply Date])

2) Count({<[Survey Reply Date.autoCalender.Month] = { 'May' } >} [Survey Reply Date])

3) Count({<[Survey Reply Date.autoCalender.Month] = {"May" } >} [Survey Reply Date])

However, when multiple months are selected in the master calender Filter Panel,  the results are as of below

1) Count({<[Survey Reply Date.autoCalender.Month] = {-} >} [Survey Reply Date])

2) Count({<[Survey Reply Date.autoCalender.Month] = {'-'} >} [Survey Reply Date])

3) Count({<[Survey Reply Date.autoCalender.Month] = {"-"} >} [Survey Reply Date])

Will you be able to provide an explanation on why does it evaluate $(=Month) to be equal to  "-" when multiple months are selected? 

[hic · Accepted solution]

To understand what happens here, you need to understand dollar expansions and aggregations:

1) A dollar expansion is "global", meaning it doesn't respect a chart dimension. It is calculated BEFORE the expression (the measure) is parsed. See more on https://community.qlik.com/t5/Design/The-Magic-of-Dollar-Expansions/ba-p/1471979

2) All calculations are aggregations, and if you haven't written an aggregation function, the Qlik engine will use Only(). See more on https://community.qlik.com/t5/Design/Use-Aggregation-Functions/ba-p/1475833

Your dollar expansion $(=Month) will thus be evaluated as $(=Only(Month)) which is NULL if there is more than one Month. And this is displayed as '-'.

So, which quotes to use here is almost irrelevant.

Count({<[Survey Reply Date.autoCalender.Month] = {$(=Month)} >} [Survey Reply Date])
will evaluate $(=Month) and make that selection in [Survey Reply Date.autoCalender.Month]

Count({<[Survey Reply Date.autoCalender.Month] = {May} >} [Survey Reply Date])
will select 'May' in [Survey Reply Date.autoCalender.Month]

Count({<[Survey Reply Date.autoCalender.Month] = Month >} [Survey Reply Date])
will pick up the selection in the field "Month" and make the same selection in [Survey Reply Date.autoCalender.Month