HOW TO CREATE A QLIK "AD HOC" REPORT AND CALCULATE COST

[gabroteddy]

Hi,

I apologize first of all if I ask for elementary things.

The question is...i have a articulate database that contain a lot of information in different tabels.

I perfectly create the script that gives me all the information necessary to calculate what i want...but i have a lot of problems to create a dinamic table that give me instantly all the information to calculate the costs of a haemodynamic procedure.

Currently I have a table that provides in output the results as shown in the attached excel (SHEET 1-TEST_FROMAT_ACTUAL).

The result is provided by a qlik table configured as visible in the attached screenshots (table_1,table_2, table_3, table_4).

NOW I HAVE 2 OBJECTIVES:

--1--

Because each procedure have a different number of fields utilized...(es. is possible that there are 4 operator, 10 material utilized, 5 procedure executed....and in another procedure 2 operator, 5 material utilized, 3 procedure executed) ...I MUST CREATE A TABLE THAT DYNAMICALLY INSERT ONCE IN THE ID ROW ALL THE FIELDS (The desidered output is showed in the attached excel (SHEET 2-TEST_FROMAT_IDEAL).

--2--

Because the fields are entities without economic value, I have to calculate the value of each field a posteriori!!!

And so...

2.1 I must create a table or excel that assign an economic value to each field?? How I can do it and how i can insert this information inside the qlik table??

2.2. I have to be able to calculate every economic value as shown  in the attached excel (SHEET 3-TEST_FROMAT_IDEAL_FINAL)...How i can put this information inside the qlik dashbboard?

I hope that someone can help me!!!

TY so much.

[chrismarlow]

Hi,

Looking at your desired output it is possible to get some of the way there using calculated dimensions for some of the numbers, rather than fields, like the below;

In my toy application I have linked 3 tables with different levels of granularity with IDs (shown in the tables at the top), rather that joining.

This is one way of approaching this, there are others, check out this & similar Fact-Table-with-Mixed-Granularity.  Linking then you can then either join or associate the values required for your calculations (for simplicity I've just put in Cost1-3).

One think that is worth challenging is how the end users will interact with the rest of the data in the model & would there be a better way of allowing them to see/work with these numbers without recreating the Excel (weaning users off just wanting the same thing as Excel is hard, but can pay off over time).

Cheers,

Chris.

[gabroteddy]

Hi Chris,

thank you for the reply.

Unfortunately it is not exactly what I expect.

In particular, my first goal is to obtain a table that for a single primary key (not 2 like in your example) could have more than one row for the other values ​​preloaded via script like the example in excel.

But now the result of the qlik dashborad is like in the attached file HAEMODEXAMPLE.qvw...in wich you ca see that there are replicated field (es.material or pharma).

How i can resolve?

 After I have solved the problem of formatting the table I will deal with the question of cost calculation.

Regards

[chrismarlow]

Hi,

I only have personal edition, so I can't open your application. Not sure I follow what you mean about the 2 primary keys.

If we take the first 3 columns where there is a hierarchical relationship;

Then if I do some find/replace stuff in my toy model then I can get;

However looking at the columns to the right that would seem to break down - the procedure code and operator type look like they follow a different hierarchy. I would also note that your materials appear in more than one of the pharma/procedure/operator buckets.

I am sure it is possible to model the two roll ups, although think you will struggle to get the exact output you want in the table & the modelling may make other analysis more difficult … 

Cheers,

Chris.