Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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))
Can you post a qlikview document that demonstrates the problem?
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.
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.