Two tab Cycled Excel Report with different Granularity

[abhimali]

Hi All,

The data I have is associated with Healthcare organization, I have data for about 400 Providers which are mapped to 35 Locations and I am trying to create a Qlik Sense App based NPritnting Excel report with two excel tabs namely Provider and Location for each provider. In short, I am trying to create 400 Excel reports with a Provider and his/her respective Location tab. I am using Cycle feature to cycle through provider dimension which gives me 400 reports. However, all the fields/metrics on the Location tab also get values of the Provider level. 

Let me explain, in this report, I have approximately 50 Metrics which I want to show by Location in the Location tab of the report and by Provider in the Provider tab of the report. For example, Let's say Location-1 has seen 500 Patients and out of this population 20 were seen by Provider-A. When I create a Cycled Report for Location-1 and Provider-A, I should be able to see Metric #Patients as 500 on the Location tab and 20 on Provider Tab, but in the current Cycle configuration, I see 20 (#Patients seen by provider) on both Tabs.

Does anyone know how to achieve this?  

Also, I know I could create 35 different reports for locations and 400 reports for providers and combine them, but then how would I map & merge each location report with its respective provider in NPrinting?   

Any help would be much appreciated.

Thanks,

Abhi

[Lech_Miszkiewicz]

can you specify where are you using cycle functionality vs level functionality vs page functionality.

As it seems to me that you are talking about all of them describing them as "cycle" which confuses what you are trying to achieve.

My assumption however is that you are using PAGE functionality with Location field in it. Instead of Field create in Qlik Sense/QlikView Chart/table with Location as dimension and measures relevant to your report. Then refresh metadata and use TABLE as PAGE object in your NPrinting template. This approach will allow you to PAGE over those locations which are returning values 

The other things are:

• what is the version of your QlikView or QlikSense & NPrinting
• please provide screenshots of your template as in this case it is crucial to understand how you have built it. 

[abhimali]

Hi Lech,

Thanks for your reply.

I am sorry, the explanation wasn't complete.  I am using Qlik Sense Nov 2018 and same version for NPrinting. In my example, I am using 2 fields Location which has 35 values and Provider which has 400 values. 

I am not using PAGE or LEVEL functionality for Excel Report because if I use page functionality based on location I will get 35 tabs or if I use Level then I will get 35 levels on the same sheet in the final report correct? and I do not want that. What I want is to create Excel reports equivalent to the number of  Providers(i.e. 400) and I achieve this by using cycle functionality on create report page. I have set Provider  & Location as fields to cycle through

Also, I have created an Excel Template with two tabs in it one for Location & another for Provider field and I am using it as a custom template in NPrinting. 

I am importing #Patients Metric which is a KPI object in Qlik Sense as a Table object in NPrinting. Now, when I drag this Table object on the Excel Template on each Tab (Provider & Location ) and run the report. I want #Patient metric to be sliced by Provider field on the Provider Tab and by Location Field on the Location Tab.  I am getting correct values for #Patients on Provider Tab but unfortunately not for the Location though. I am getting the same value on the Location tab.

Currently, I have managed to overcome this by duplicating the #Patients KPI and setting it to not respect the Provider field. I am all ears if there is a better way to do this. 

All the metrics I want to import are KPI objects in Qlik Sense and I am planning to create two separate Tables of Metrics for Location & Provider so that I can import just two tables instead of importing these metrics one by one from KPIs.

Please find attached Excel template example.