topic Re: Computing Expressions on Script Level in QlikView

Computing Expressions on Script Level

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I have almso 20-25 expressions calculated on UI level across various Charts and Graphs for 20 Domensions (almost). The Application is getting affected because of the loading or refreshing time of charts.

I want to calculate all or may be few expressions in UI level. Please guide me accordingly.

Following are a few expressions used in UI level--

1. Annual Loss Amount - (Sum({<Year_Quarter = {"$(=Max(Year_Quarter))"}>}Disitnct Annual_Loss)

2. Loss Amount Ratio - (Sum(Disitnct Annual_Loss) + Sum(Disitnct Risk_Loss) + Sum(Disitnct Settlement_Cost)) / Sum(Disitnct Premium_Amount)

3. Insurance Premium - Sum({<Year_Quarter = {"$(=Max(Year_Quarter))"}>}Disitnct Policy_Premium)

4. Premium Ratio - Sum({<Year_Quarter = {"$(=Max(Year_Quarter))"}>}Disitnct Policy_Premium) / Sum({<Year_Quarter = {"$(=Max(Year_Quarter))"}>}Disitnct Premium_Amount)

Likewise a few more. I am thinking to write above calculations with Group By all the Dimensions.

Please suggest correct way.

Thanks in Advance.

Re: Computing Expressions on Script Level

sunny_talwar — Thu, 06 Oct 2016 19:07:38 GMT

It can be easier to help if you can provide a sample to work with.

Re: Computing Expressions on Script Level

Peter_Cammaert — Thu, 06 Oct 2016 21:04:27 GMT

What exactly is the meaning of all those DISTINCT keywords? Does it mean that for example for every combination of dimension values, you can have only one $500 amount?

Maybe you should first do something about your data model?

Re: Computing Expressions on Script Level

Anonymous — Fri, 07 Oct 2016 06:16:16 GMT

I have d correct data model. Chked with ETL team. Distinct we are using because we have same figure for each location.

Customer 1 has 5 location but the premium they are paying is same.

Re: Computing Expressions on Script Level

Anonymous — Fri, 07 Oct 2016 06:16:32 GMT

I will surely do it.

Re: Computing Expressions on Script Level

Anil_Babu_Samineni — Fri, 07 Oct 2016 07:18:23 GMT

Might be spell is wrong, Try this DISTINCT instead of Disitnct

1. Annual Loss Amount - (Sum({<Year_Quarter = {"$(=Max(Year_Quarter))"}>} DISTINCT Annual_Loss)

2. Loss Amount Ratio - (Sum(DISTINCT Annual_Loss) + Sum(DISTINCT Risk_Loss) + Sum(DISTINCT Settlement_Cost)) / Sum(DISTINCT Premium_Amount)

3. Insurance Premium - Sum({<Year_Quarter = {"$(=Max(Year_Quarter))"}>} DISTINCT Policy_Premium)

4. Premium Ratio - Sum({<Year_Quarter = {"$(=Max(Year_Quarter))"}>}DISTINCT Policy_Premium) /Sum({<Year_Quarter = {"$(=Max(Year_Quarter))"}>}DISTINCT Premium_Amount)

Re: Computing Expressions on Script Level

Anonymous — Fri, 07 Oct 2016 09:19:45 GMT

Do you want it to be done in script .

Re: Computing Expressions on Script Level

Anonymous — Fri, 07 Oct 2016 12:40:23 GMT

Looks like you are aggregating things at the Year_Quarter level and this could certainly be done in the script using group by.

I am just finishing off redesigning a dashboard that displays many things at a month aggregation level.

The original had about 50 million data rows and the front end was stuffed full of horrendous expressions [albeit very cunning some of them] and its performance was bad, plus it chewed up loads of RAM & CPU.

My new version has about 70 thousand rows and the front end only has simple expressions. It is now lightening fast and consumes negligible RAM & CPU. [Both cost money and CPU cycles consume electricity, making heat that needs air con cooling that takes more power that all contributes to global warming as well reducing the business's profit]

My personal general rules of thumb are :

If it can be done in the script then do it in the script
If your front end is full of over complex expressions then the data model is in need of improvement

One does need to think about what level of granularity is needed in the front end:

If you need to drill down to transaction level then you need to load your data at the transaction level so aggregation to a higher level would not be appropriate
But in my scenario the lowest level of front end drill down needed was month, so it was a perfect candidate for month level aggregation in the script
I also had a few other dimension to consider, such as Business Unit, Customer, Type, Function - So the group by was actually on these dimensions as well as month

But not knowing the details of your data model & needs I cannot make any definitive suggestions for your specific scenario.

Re: Computing Expressions on Script Level

Peter_Cammaert — Fri, 07 Oct 2016 16:15:20 GMT

That means that you can never sum Amounts or Costs per region for example? That's a very weird data model. Why do you repeat all those amounts? The Distinct keyword by itself will slow down your document for no reason at all.

Re: Computing Expressions on Script Level

Anonymous — Sat, 08 Oct 2016 11:08:53 GMT

yes it is DISTINCT only..QV takes it automatically when typed

Re: Computing Expressions on Script Level

Anonymous — Sat, 08 Oct 2016 11:09:10 GMT

yes