topic Re: Table Creation Help in App Development

Table Creation Help

richardm90 — Wed, 07 Mar 2018 14:13:47 GMT

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,

Re: Table Creation Help

rubenmarin — Wed, 07 Mar 2018 14:21:04 GMT

Hi Rick, if you add Source as dimension, each row will count the associated source values. Is not working like that?

Re: Table Creation Help

shraddha_g — Wed, 07 Mar 2018 14:25:11 GMT

You can try with Pivot table.

Row - Source

Column - [Submission date.autoCalendar.YearMonth]

Measure - count ([Submission Date])

Re: Table Creation Help

richardm90 — Wed, 07 Mar 2018 14:25:35 GMT

Hi Ruben,

It just returns the total number of patients for that month in each field rather than filtering them by source i.e.:

Source	Feb 2018	March 2018
Web Form	20
Phone	20
Email	20

Re: Table Creation Help

rubenmarin — Wed, 07 Mar 2018 14:30:23 GMT

This is usually related with data relationships... Is each record associated with only one date and source? Maybe source is not related with dates, so each one exists in all dates (or none)

You can create a simple table with source, date and recordId to check.

Re: Table Creation Help

richardm90 — Wed, 07 Mar 2018 14:33:44 GMT

Thanks for the help here guys, I think that is the issue, the data source with the sources column isn't linked to the other sources for bookings/appointments. I'll have a go at getting this sorted and retest and see if it works.

Thanks again for your help on this.

Re: Table Creation Help

richardm90 — Wed, 07 Mar 2018 16:01:48 GMT

Just a quick one in relation to this. How do I create a column and only display certain values? (without applying a filter)

I.e. If I want to only show fields where the course is Email?

So when I'm creating a row, in the field I'm putting Source = "email"

Re: Table Creation Help

rubenmarin — Wed, 07 Mar 2018 16:06:53 GMT

Usually best performace is filtering using set analysis in expression:

Count(

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

[Submission date])

Re: Table Creation Help

richardm90 — Thu, 08 Mar 2018 10:19:23 GMT

Thanks again for the help on this, all looking good so far!

One more thing I need abit of advice on:

We have the need to store all of our KPI's in the same table so that we can export them all together rather than having to click on every individual table and export to excel.

This is what we need:

Currently these are created separately i.e. Table 1: Source of Referal, Table 2: Patients seen within 5 working days.

Is it possible to combine these into the same pivot table?

Failing this; is there a way to export multiple tables on the same sheet as an excel/csv file at the same time?

Re: Table Creation Help

rubenmarin — Thu, 08 Mar 2018 10:53:08 GMT

Hi Ric, to export many tables at once I think you need a macro.

To use in the same table maybe a quick fix can be done:

// Add an inline table with KPIs

KPI:

LOAD * Inline [

IdKPI, KPI

1, Source of referal

2, ...

3, ...

];

In table add dimension KPI and a calculated dimension like:

If(IdKPI=1, Source)

The expression can use the idKPI to select the expression:

Pick(IdKPI

, Expression for kpi1

, Expression for kpi2

, Expression for kpi3

)

Maybe needs aggr(:

Aggr(Pick(

...

), IdKPI)

Re: Table Creation Help

richardm90 — Thu, 08 Mar 2018 11:08:22 GMT

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?

Re: Table Creation Help

richardm90 — Mon, 12 Mar 2018 09:36:56 GMT

Hi Ruben,

Any further help on this would be much appreciated.

Re: Table Creation Help

rubenmarin — Mon, 12 Mar 2018 18:41:49 GMT

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.

Re: Table Creation Help

richardm90 — Tue, 13 Mar 2018 15:42:21 GMT

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?

Re: Table Creation Help

rubenmarin — Tue, 13 Mar 2018 16:30:24 GMT

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.

Re: Table Creation Help

richardm90 — Tue, 13 Mar 2018 16:52:33 GMT

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.

Re: Table Creation Help

rubenmarin — Tue, 13 Mar 2018 21:58:53 GMT

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

Re: Table Creation Help

richardm90 — Wed, 14 Mar 2018 00:09:24 GMT

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.

Re: Table Creation Help

rubenmarin — Wed, 14 Mar 2018 07:39:22 GMT

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.

Re: Table Creation Help

richardm90 — Thu, 15 Mar 2018 10:26:01 GMT

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?