topic Expression Issue Help ! in QlikView

Expression Issue Help !

amit_saini — Wed, 02 Dec 2015 09:07:55 GMT

Ho Folks,

I'm not sure if this is because of my data model , but I'm facing issue with below bar chart.

Instead of showing all Defect_Desc. , bar is only showing "Appearance".

Dimension: =if(Month<>Month(today()),SubField(Defect_Description,'-',1))

Expression:

sum(aggr(Sum({<Complaint_Acceptance-={4,5,6,7}>} QTY_PPM*Top_Pos_Flag) /

(Sum ({<Complaint_Type = {1, 3}>} QTY_Delivered_TOTAL) + Sum ({<Complaint_Type = {2, 7}>} QTY_Received_TOTAL)) *1000000,%MANDANT_Key))

Kindly help!

Thanks,

Re: Expression Issue Help !

tresB — Wed, 02 Dec 2015 09:20:15 GMT

Could you post your qvw with sample data?

Re: Expression Issue Help !

MK_QSL — Wed, 02 Dec 2015 09:24:04 GMT

Try Calculated Dimension as below

Aggr(if(Month<>Month(today()),SubField(Defect_Description,'-',1)),Defect_Description)

Re: Expression Issue Help !

marcus_sommer — Wed, 02 Dec 2015 09:29:05 GMT

I would probably not use this kind of calculated dimension and adjust and/or split the field Defect_Description within the script and put the condition within the expression maybe like this:

Date = {"<date(monthstart(today(), 'YourFormat')"}

Further I would use rangesum(sum1, sum2) instead of sum1 + sum2 and maybe a further alt() to catch possible NULL or errors.

- Marcus

Re: Expression Issue Help !

amit_saini — Wed, 02 Dec 2015 10:34:55 GMT

Hi Tresesco,

PFA

Thanks,

Re: Expression Issue Help !

amit_saini — Wed, 02 Dec 2015 10:35:24 GMT

Hi Manish,

Same issue

Thanks,

Re: Expression Issue Help !

sunny_talwar — Wed, 02 Dec 2015 11:28:50 GMT

Does this look like something you want?

I added NODISTINCT to the expression:

=Sum(Aggr(NODISTINCT Sum({<Complaint_Acceptance-={4,5,6,7}>} QTY_PPM*Top_Pos_Flag) /

(Sum ({<Complaint_Type = {1, 3}>} QTY_Delivered_TOTAL) + Sum ({<Complaint_Type = {2, 7}>} QTY_Received_TOTAL)) *1000000,%MANDANT_Key))

Re: Expression Issue Help !

amit_saini — Wed, 02 Dec 2015 11:35:22 GMT

Hi Sunny,

No!

Sum out of bars should be 97.

Thanks,

Re: Expression Issue Help !

sunny_talwar — Wed, 02 Dec 2015 11:38:32 GMT

You mean if we sum all the bars it would total to 97?

Re: Expression Issue Help !

amit_saini — Wed, 02 Dec 2015 11:40:11 GMT

Yes!

Re: Expression Issue Help !

sunny_talwar — Wed, 02 Dec 2015 11:48:01 GMT

The output is what you want, but not sure if this will continue to work with selections. I will let you test it on your end:

Expression:

Avg(Aggr(NODISTINCT Sum({<Complaint_Acceptance-={4,5,6,7}>} QTY_PPM*Top_Pos_Flag) /

(Sum ({<Complaint_Type = {1, 3}>} QTY_Delivered_TOTAL) + Sum ({<Complaint_Type = {2, 7}>} QTY_Received_TOTAL)) *1000000,%MANDANT_Key))

Re: Expression Issue Help !

Wed, 02 Dec 2015 11:50:29 GMT

Try this:

Sum({<Complaint_Acceptance-={4,5,6,7}>} QTY_PPM*Top_Pos_Flag)

/ (Sum ({<Complaint_Type = {1, 3}>} total QTY_Delivered_TOTAL) + Sum ({<Complaint_Type = {2, 7}>} total QTY_Received_TOTAL)) *1000000

Re: Expression Issue Help !

Wed, 02 Dec 2015 11:54:21 GMT

Aggr is very sensitive - it will put all the answer into first occured dimension if this dimension is not in it's dims list. Also Aggr produses huge impact on perfomance. Try to avoid it. the same impact also is produced by the calculated dimensions. Here your dimension can be simply transformed into additional Set Analysis like $*1<Month-={"=Month(Today(1))"}> and make in model your second part (subfield) of the dimension precalculated.

Re: Expression Issue Help !

amit_saini — Wed, 02 Dec 2015 11:54:33 GMT

Sorry Sunny, what I'm looking here is to get overall sum as 97 from all bars like "Assembly" ,Damaged Component, Customer Dis.....etc

Thanks,

Re: Expression Issue Help !

sunny_talwar — Wed, 02 Dec 2015 11:58:28 GMT

Hahahaha yes you are right, I am an idiot. I will re-look at it

Re: Expression Issue Help !

sunny_talwar — Wed, 02 Dec 2015 12:12:58 GMT

This?

Expression:

Sum(aggr(NODISTINCT Sum({<Complaint_Acceptance-={4,5,6,7}>} QTY_PPM*Top_Pos_Flag),%MANDANT_Key))/

(Sum(TOTAL <[%MANDANT_Key]> Aggr(NODISTINCT Sum ({<Complaint_Type = {1, 3}>} QTY_Delivered_TOTAL), %MANDANT_Key)) +

Sum(TOTAL <[%MANDANT_Key]> Aggr(NODISTINCT Sum ({<Complaint_Type = {2, 7}>} QTY_Received_TOTAL), %MANDANT_Key))) * 1000000

Re: Expression Issue Help !

amit_saini — Thu, 03 Dec 2015 06:06:31 GMT

Sunny ,

This calculation is working fine when I'm selecting any individual plant , but overall this is not working 100%

Example : All these 6 plants comes under "NA" , so if you are selecting any one of these plants then ur expression is working fine, but in case of overall NA =372 , calculates sum from ur expression is not equal to 372.

Thanks,