Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
madeleine_josef
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
jontydkpi
Not applicable

Re: Question regarding an expression

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
madeleine_josef
Not applicable

Re: Question regarding an expression

I tried, but got the following values:

Now the total is incorrect as well.

Yes, the months are the same.

Not applicable

Re: Question regarding an expression

Hi,

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

jontydkpi
Not applicable

Re: Question regarding an expression

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
madeleine_josef
Not applicable

Re: Question regarding an expression

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

Re: Question regarding an expression

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
Not applicable

Re: Question regarding an expression

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