topic Need help regarding calcalated measures in QlikView

Need help regarding calcalated measures

Wed, 28 Aug 2013 08:12:08 GMT

Hi ,

I have one calculated meaure (Avg TAT) for that we are using definition , and the final result will be in Hours ,

(((Sum(TOT_INTR_MIN*60)+sum(PPS_anr_tat_hrs__c*3600))/count(distinct(if(UPPER(Status)='CLOSED' or 'COMPLETED',Case_Number,1))))/3600)

TOT_INTR_MIN--> values are there in Minutes(so converted into Sec.)

PPS_anr_tat_hrs__c--> values are there in Hours (so converted into Sec.)

while using that definition not getting exact value(please look at below highlighted values in the table)

VAEC	88.38
KOREA	43.57
SWEDEN	8,125.59
DENMARK	7,722.90
ISAREL	7,205.16
BELGIUM	6,730.11
FINLAND	6,404.13
SPAIN	6,397.94
FRANCE	6,038.79
SWITZERLAND	5,916.24

Can any one help me here ?

Thanks & regards,

Re: Need help regarding calcalated measures

abeyphilip — Wed, 28 Aug 2013 08:34:40 GMT

Hi Shobha,

Please share what you are expecting for the highlighted values.

A test model / data would help others to look into this.

Regards,

Abey

Re: Need help regarding calcalated measures

Wed, 28 Aug 2013 08:35:55 GMT

Not sure about why you consider those values not exact, but you seem to have one error in the expression:

(((Sum(TOT_INTR_MIN*60)+sum(PPS_anr_tat_hrs__c*3600))/count(distinct(if(UPPER(Status)='CLOSED' or 'COMPLETED',Case_Number,1))))/3600)

Should be:

(((Sum(TOT_INTR_MIN*60)+sum(PPS_anr_tat_hrs__c*3600))/count(distinct(if(UPPER(Status)='CLOSED' or UPPER(Status)='COMPLETED',Case_Number,1))))/3600)

Re: Need help regarding calcalated measures

suniljain — Wed, 28 Aug 2013 08:38:32 GMT

Can you share application with dummy data ?

Re: Need help regarding calcalated measures

Wed, 28 Aug 2013 08:59:48 GMT

Hi ,

Thanks for the reply ,

my expected value is

SPAIN	6,397.94--->6,398.34
FRANCE	6,038.79---->6,039.19

values shoud be in hours ..

Thanks & Regards,

Re: Need help regarding calcalated measures

Wed, 28 Aug 2013 09:11:16 GMT

Ok, understood, but I think you are focusing wrong the data you want.

SPAIN	6,397.94
FRANCE	6,038.79

This is not wrong, you just have to represent it as an interval in every graph you want, and it will show up as a an hour data. Just pick the 'interval' representation option in the 'number' tab of your graph, and, as it will expect day related expression, just div the expression with a 24:

hour/24

this will work and your graph will show:

SPAIN 6,397:56 h

Re: Need help regarding calcalated measures

abeyphilip — Wed, 28 Aug 2013 10:24:10 GMT

Hi Shobha,

The conversion of 6,397.94 to 6,398.34 may not be correct.

You have used a mathematical calculation of division by 3600. There is no time calculation here.

In this way, ".100" will be 1 hour.

".94" you can consider it as 94% of an hour, as IOSO has pointed out this comes to 56 minutes and not 1 Hour.

Similarly, 5916.24 for Switzerland is not 5916 hours and 24 minutes, but 5916 hours and 15 minutes.

So you already seems to have the right value.

regards,

Abey