First of all, hello everyone. I'm new to Qlik and I couldn't get out of the problem inside.
I have one table: Fact.Then I created the Transaction_Number table and added two sum fields using group by and it worked at first.
However, when I use a date filter on the chart, the calculations do not work correctly because I cannot create a key between the two tables.
When I create a unique key as follows and add it both tables, I get an "invalid expression" error.
(Region Code"&'_'&"Branch Code"&'_'&"Completed + Cancel" as %KEY)
Moreover, while I want the group by command to be applied only to fields that contains sum, but it also applies group by to other fields I add to the table, and the calculation gets confused.
Here is the code. I request your kind help.
FACT:
LOAD
Year("Transaction Period") as Year,
Month("Transaction Period") as Month,
Date(MakeDate(Year("Transaction Period") ,num(Month("Transaction Period")), 'YYYY.MM') as Period,
"Region code",
"Branch Code",
"Branch Name",
"Region Name"
"Section Name",
"Process Name"
"Total Refund",
"Completed + Cancel",
"Return Rate"
FROM [………………………………);
TRANSACTION_NUMBER:
LOAD
Sum("Completed + Cancel") as Number of Transactions,
Sum("Total Refund")/Sum("Completed + Cancel") as Refund_Rate
You need to include the grouping-fields also within the aggregation load because otherwise you creates just one global value - in your case in may look like:
LOAD Key,
Sum("Completed + Cancel") as Number of Transactions,
Sum("Total Refund")/Sum("Completed + Cancel") as Refund_Rate
Resident FACT
Group by "Key"
Beside this it might be more appropriate not to aggregate this kind of measures within the script else doing it within the UI which aligns the calculations to the selections and if you want to ignore some dimensionality in your calculation you could use the TOTAL <optional dimensions> statement.
You need to include the grouping-fields also within the aggregation load because otherwise you creates just one global value - in your case in may look like:
LOAD Key,
Sum("Completed + Cancel") as Number of Transactions,
Sum("Total Refund")/Sum("Completed + Cancel") as Refund_Rate
Resident FACT
Group by "Key"
Beside this it might be more appropriate not to aggregate this kind of measures within the script else doing it within the UI which aligns the calculations to the selections and if you want to ignore some dimensionality in your calculation you could use the TOTAL <optional dimensions> statement.
