topic Re: Loading Calculated Facts in QlikView

Loading Calculated Facts

Anonymous — Mon, 27 Oct 2014 07:03:48 GMT

Is it possible to load calculated 'Facts' when you are loading a table. I have a table with the following fields and records:

[Drug Name] and [Repeat or Original]

Frusemide Original

Frusemide Repeat

Frusemide Original

Frusemide Repeat

Amlodipine Original

Amlodipine Repeat

Amlodipine Original

i want to be able to load calculated fields based on the above records as follows:

Total Prescriptions (TRx) which is an aggregate count of all Original and Repeat records for all [Drug Name] field
New Prescriptions (NRX) which is an aggregate count of all Original only records for all [Drug Name] field
Total Drug Prescriptions (TDRx) which is an aggregate count of all Original and Repeat records for a particular [Drug Name]
New Drug Prescriptions (NDRx) which is an aggregate count of all Original only records for a particular [Drug Name]

if this is not possible, what is the alternative approach?

I hope my question is clear. Thanking you in advance.

Regards

Chris

Re: Loading Calculated Facts

ecolomer — Mon, 27 Oct 2014 07:33:49 GMT

Is this?

Re: Loading Calculated Facts

Mon, 27 Oct 2014 08:28:49 GMT

Create a Straight Table and put [Drug Name] as Dimension and below as expressions:

Total Prescriptions (TRx) which is an aggregate count of all Original and Repeat records for all [Drug Name] field

=count([Repeat or Original])

New Prescriptions (NRX) which is an aggregate count of all Original only records for all [Drug Name] field

=count({<[Repeat or Original] = {"Original"}>}[Repeat or Original])

Total Drug Prescriptions (TDRx) which is an aggregate count of all Original and Repeat records for a particular [Drug Name]

This will be same as First expression, for each Drug Name, the count will be displayed.

=count({<[Drug Name]= {"Frusemide"}>}[Repeat or Original])

New Drug Prescriptions (NDRx) which is an aggregate count of all Original only records for a particular [Drug Name]

Same as expression 2.

=count({<[Repeat or Original] = {"Original"},[Drug Name]= {"Frusemide"}>}[Repeat or Original])

Hope this was helpful.

Thanks,

Singh

Re: Loading Calculated Facts

Anonymous — Mon, 27 Oct 2014 09:34:01 GMT

Hi Enrique

Many thanks, that is correct.

Regards.

Re: Loading Calculated Facts

Anonymous — Mon, 27 Oct 2014 11:28:34 GMT

Hi Enrique

Further to my last response confirming that this is what i am looking for, can you kindly explain how you arrived at the solutions posted.

Regards.

Chris

Re: Loading Calculated Facts

Anonymous — Mon, 27 Oct 2014 21:03:08 GMT

Many thanks for your input. However, i need to be able to use the output to do further calculations in more straight table charts using calendars to view year to date, current month, previous month etc point in time analysis charts. Will this be possible if i use straight tables to calculate these fields. I have already done so in my application. Please see attached screenshot

Re: Loading Calculated Facts

Tue, 28 Oct 2014 06:03:22 GMT

Hello Christopher,

You can create Variables for generic expressions and then use them in other objects of your dashboard over different dimensions.

vTRx =count([Repeat or Original])

vNRx =count({<[Repeat or Original] = {"Original"}>}[Repeat or Original])

If you need to extend these expressions in other straight tables, then I guess you have to duplicate them there as well with other requirements embedded.

Hope this was helpful.

Thanks,

Singh

Re: Loading Calculated Facts

Anonymous — Wed, 29 Oct 2014 18:28:22 GMT

Dear Singh

Further to my last questions. I am trying to calculate the current month total prescriptions (TRx) with the following script:

Sum({$<$(vSetPreviousNMonth(0))>} Aggr(TextCount([Repeat or Original]), [Drug Code]))

This is not giving the right value. Is anything wrong with the above?

regards.

Chris

Re: Loading Calculated Facts

Anonymous — Wed, 29 Oct 2014 19:40:20 GMT

Hi Enrique

i am still waiting for your response. How did you arrive at the results posted?

Regards

Chris

Re: Loading Calculated Facts

Thu, 30 Oct 2014 06:33:19 GMT

Hello,

The the current month total prescriptions (TRx) can be calculated as :

count({<MonthSeq = {"=$(max(MonthSeq)"}>}[Repeat or Original])

where MonthSeq is the monthnumber the way u are calculating vSetPreviousNMonth

For:

Try selecting the current month from calendar and see whether he blow returns correct results or not.

Aggr(TextCount([Repeat or Original]), [Drug Code])

Re: Loading Calculated Facts

Anonymous — Thu, 30 Oct 2014 06:51:07 GMT

Hello

Many thanks, if i select current month in the calendar it is returning the correct TRx count but this value is the same as the year to date value calculated as follows:

Sum({$<$(vSetYTD)>} Aggr(TextCount([Repeat or Original]), [Drug Code]))

I do not know what is going on.

regards.

Chris

Re: Loading Calculated Facts

Thu, 30 Oct 2014 07:37:39 GMT

It will be great if you could post a sample file with expected numbers. That will help me and other members to give you prompt responses.

--Singh

Re: Loading Calculated Facts

Anonymous — Thu, 30 Oct 2014 08:46:47 GMT

Hello Singh

I managed to figure it out. I changed it to the following and it is now working:

Sum(Aggr(Count({$<$(vSetYTD)>} [Repeat or Original]), [Drug Code]))

Regards

Chris

Re: Loading Calculated Facts

Thu, 30 Oct 2014 09:27:36 GMT

Great! Feel free to mark any answer as helpful or correct to close the thread.

Thanks,

Singh