I want to show Caliculated field in UI Level (with all Values)

[madhubabum]

Hi all

I have the following Scenario :

let vStartDate=Today();

let vEndDate=AddMonths(Today(),-12);

Test:

LOAD Department_Id,

     Description,

     Ordered_Date,

     Billing_Price

FROM

(ooxml, embedded labels, table is Sheet1);

Inner Join

LOAD

     Ordered_Date,

     Description,

     Department_Id,

     Avg(Billing_Price) as Avg_Billing_Price,

     Avg(if(Ordered_Date >$(vEndDate),Billing_Price)) as Last_12_Months_Avg_Billing_Price

Resident Test    

Group By Ordered_Date,Department_Id,Description;

1. If i am using the "Fieldname : Last_12_Months_Avg_Billing_Price" , Then i am getting some "Department_Id's "
2. If i am using the expression in UI level in Expression Tab  ,  i am getting Total "Department_Id's "

Solution : I want to use the calculation in Script level & i want to display Total Department_Id's

Department_Id   Description     Avg(if(Ordered_Date >$(vEndDate),Billing_Price)) 

                                                                                 Department_Id            Avg(if(Ordered_Date >$(vEndDate),Billing_Price))                

[Gysbert_Wassenaar]

Can you post a qlikview document that demonstrates the problem?

[Gysbert_Wassenaar]

I'm afraid I don't understand what the problem is. Your Test_app.qvw file doesn't tell me anything about what might be a problem.

[Gysbert_Wassenaar]

The problem is caused because you use a 'naked' field as expression. You're not explicitly using an aggregation function like sum, avg or max when you use Last_12_Months_Avg_Labour_Hours as expression. In that case Qlikview uses the Only() function. The Only() function only returns a value if there's only one possible value of the field Last_12_Months_Avg_Labour_Hours for the combination of dimension values in the chart. And that is not true in your mode because there are often several %ORDER_DATE_KEY values per combination of Profit Center and Equipment_Model_Name. That's when the Only() function returns a null. And nulls are suppressed so those rows are not shown in your table. Simply add %ORDER_DATE_KEY as dimension to your charts and you'll see it.