Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Question regarding an expression

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!

7 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

How about:

Sum({$<PlanVersionId={'PV4000013'}>} Amount * BudgetKeyAQMFI)


Are the Months for BudgetKeyAQMFI aligned to the months for the rest of the data?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

I tried, but got the following values:

Now the total is incorrect as well.

Yes, the months are the same.

Not applicable
Author

Hi,

Is it possible to see how you link those tables? Month format for both tables?

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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:

Not applicable
Author

I think the Month value after using the month function on your First table did not match correctly the values on Budget table.

sunny_talwar

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

Why don’t my dates work?

Get the Dates Right