Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table showing budget numbers per month like this:
And now I want to change so that the numbers are not spread even across 12 months, i.e using budget keys and I have these in an excelsheet ordered like this:
How can I add the budget keyes in my expression? I tried to do it like this: sum({$<PlanVersionId={'PV4000013'}>}Amount)* Sum(BudgetKeyAQMFI)
But this does not work, it gives me the correct total sum of the budget, however the sum per month is not correct. See here:
How come the total of the budget is correct, but the numbers for each month looks incorrect?( 3330128 x 7% = 233108,96)
Thanks!
How about:
Sum({$<PlanVersionId={'PV4000013'}>} Amount * BudgetKeyAQMFI)
Are the Months for BudgetKeyAQMFI aligned to the months for the rest of the data?
I tried, but got the following values:
Now the total is incorrect as well.
Yes, the months are the same.
Hi,
Is it possible to see how you link those tables? Month format for both tables?
You need to provide more information for more detailed help. You script, your data model, sample date and/or (preferably) a sample qvf file illustrating the problem would make this easy to diagnose.
Here is the script:
LOAD
"Month" as TransactionMonth,
"MonthName" as TransactionMonthName
FROM [lib://AttachedFiles/MonthName.xlsx]
(ooxml, embedded labels, table is Sheet1);
LOAD
AlfaCompany,
TRNumber,
CompCodeTRNumberKey,
RegistrationDate,
ReceiptComment,
[TransactionDate] as Datum,
month (TransactionDate) as TransactionMonth,
year (TransactionDate) as Year,
TransactionType,
Account,
Object,
AssignmentNumber,
Value,
VATCode,
RowComment,
Currency,
ReceiptValueGroupCurrency,
TRrwRowNo,
ReceiptValueCurrency,
TRrwObjects
FROM [lib://QvdLib (alfa_qliksense)/Prod\Load\Hansa\Receipt.qvd]
(qvd)where (([Account]>=3000)and ([Account]<=3799))and ([TransactionDate]>'2015-12-31');
[Budget_amount]:
LOAD
[ClientId],
[PlanVersionId],
[Dim1],
[PeriodMM] as TransactionMonth,
[Amount]
FROM [lib://QvdLib (alfa_qliksense)/Prod\Extract\Effectplan\Budget_amount.qvd]
(qvd)where (([Dim1]>=3000)and ([Dim1]<=3799));
LOAD
TransactionMonth,
BudgetKeyAQMDK,
BudgetKeyAQMNO,
BudgetKeyAQMFI,
BudgetKeyAQMSE
FROM [lib://AttachedFiles/BudgetKeys2017.xlsx]
(ooxml, embedded labels, table is Sheet1);
And here is the data model:
I think the Month value after using the month function on your First table did not match correctly the values on Budget table.
The problem might be related to the interpretation of TransactionMonth within all the tables. I would suggest you to go through these links to make sure your Month field is read as dual field by QlikView so that your connections are made appropriately here