Table Creation Help

[richardm90]

Hi All,

I'm trying to create a table in Qlik Sense to show the number of patients each month and how their appointment was booked as below:

Source	Feb 2018	March 2018
Phone
Email
Web Form

I created the Month columns as measures. Below is the formula for Feb 2018 which counts the total number of appointments and filters those that were booked in Feb.

Count(

{<[Submission date.autoCalendar.YearMonth]={"2018-Feb"}>}

[Submission date])

How do I also filter it by each source?

Thanks,

[richardm90]

Thanks again for your quick response Ruben, is it possible to create Macro's in Qlik Sense cloud?

I Assume an inline table is added within the data load editor?. Is there a step-by-step guide somewhere for creating this?

[richardm90]

Hi Ruben,

Any further help on this would be much appreciated.

[rubenmarin]

Hi Ric, I think the macro option is not possible in Cloud.

Yes, the inline should be added in script editor, I attach a simple sample with dummy expressions to get the idea.

The IdKPI identifies wich expression to pick, dimensionality is used to decied when draw the value (on first or in 2nd dimension).

And desabling the option toshow zero values to hide rows without data.

[richardm90]

Hi Ruben,

Thanks a lot for this! After attempting this in my app and looking into the inline functionality. It is my understanding that this only works if you are typing the data in manually within the data loader? Our app needs to pull data from xls files. Is there another way I can get around this? 

[rubenmarin]

It was only an example, data can be pulled from excel files.

Inline is only a source that identifies data manually written but the source can be switched to Excel.

[richardm90]

Hi Ruben,

Yes we have been pulling data in from Excel files previously. The issue is that each column needs to have multiple calculations associated with it. i.e. Jan 2018 needs to show the figures for multiple KPI's at the same time. 

[rubenmarin]

I'm not sure of what's the issue, in the sample Jan-2018 is calculating 3 different expressions. Can you share a sample?

[richardm90]

Hi Ruben,

This is all starting to make sense now! Thank you.

In a pivot table how do I prevent rows with no sub-rows from having drop-downs?

Currently, all rows even those without subs expand and when they do the data changes.

[rubenmarin]

Hi Ric, I don'0t think that's possible, it there is a dimension below it will show the button to expand.

Using dimensionality() and unchecking the option to show zero values (in the add-ons section) can be a workaround.

[richardm90]

I cant seem to be able to get the pick and  dimensionality() functions working. As a workaround for the measure I have used bundled if statements:

=if(IDKpi='1' OR IDKpi='4' OR IDKpi='5',Count([Submission date]),

if(IDKpi='2', Sum([Submission date]),

if(IDKpi='3',count({<[Is this an urgent appointment?]={'YES'}>} [Start Time]))

))

Could you help me convert this with the pick and dimensionality() functions?