Is it possible to use two different alternate states in one formula e.g. sum()?

[#1]

Hello, 

In order to solve a particular question, it is probably necessary to work with two alterante states. The first one is to indicate the period from which certain quantities of materials are to be taken. The second specifies the prices for these materials. 

In the extract of the data model, one recognizes that the batch (Charge) is linked to the production date (Datum), the quantities of a certain material (MengeChargiert) in a batch has also a reference to a further date range, in which the price (Preis) for the materials (SorteID) are valid (year_monthr => Preisgültigkeit). In principle, there is always a corresponding entry in the price table for all varieties used throughout the considered  period. With this it is possible e.g. to answer the question how much a batch produced in May would have cost if we consider prices from August.

In addition to the alternate state "aBetrachtungszeitraum" (period to analyse) a second alternate state "aPreisgültigkeit" (price valid in Month XX) was now defined.

In a diagram table with the dimensions Qualität (grade) and SorteID (material), this works well. I can display the price in a column with reference to the alternate state "aPreisgültigkeit" and in another column the quantity (MengeChargiert) referenced to the alternate state  "aBetrachtungszeitraum", so I also can calculate the product of price and quantity. Example:

Now to the real problem.

I do not want to use the colum "SorteID" in this diagram table, it should only be evaluated in relation to the grade (Qulität" . Only the numbers marked in red should appear in the table, for example:

In this case, an aggregation function must be used. The formula

sum ({aBetrachtungszeitraum} <SorteID> MengeChargiert* only ({aPreisgültigkeit} Preis))

unfortunately does give the desired result. 

Do I have a thought mistake here and is there another way to solve the problem? 

Thanks for any help 

Dirk

P.S.: This is a transcript from the german userforum: https://community.qlik.com/message/1274469#1274469

[tresesco]

For Alternate State possibility check here: Is possible to use alternate States with set analysis

[#3]

Dear Treresco,

thanks for your answer. Of course you can use alternate states with set analysis. But the question was, is it possible to use two different alternate states in one formula.

e.g.

     sum({AS1}  quantity * price)

is different from

     sum({AS1} quantity)*sum({AS2} price)

but this gives wrong results.

The correct answer should be

     sum( {AS1} quantity  *   {AS2} price )

but this syntax is not allowed.

Regards

Dirk

[tresesco]

Try like:

sum( {AS1} quantity  * Aggr(Only( {AS2} price ),price))

Or, may be even this (I did not try this though):

Sum({AS1 <price= [AS2]::price>} quantity*price)

[marcus_malinow]

Hi Dirk,

this is possible, I have done similar in the past. You may have to use AGGR to achieve it though. For example:

sum(

    Aggr(

        sum({AS1} quantity) * sum({AS2} price),

        Qualitat, SorteID)

    )

Marcus