2 Master Measures in Pie Chart

[Timerichhh]

Hello guys,

I am new to Qlik and Data Analytics but I am trying to improve my skillset as a student. Therefore, I hope I selected the right forum.

Short context:

For a loan, I want to calculate the time that has passed (of the total loan duration) and the duration that is still left. If a loan started on 01.07.24 and ends on 01.01.26, today (05.07.25), 67.41% of the loan has already passed. In my data model, I want to get the average of these durations for selected (real estate) objects (ObjektKey). There are multiple loans for each object, therefore I have to get the least active loan (this works).

My approach:

I created two master measures;

1. LoanDurationPassed:
Avg(
    Aggr(
        (Today() -
            FirstSortedValue(
                {<[Darlehen_Stammdaten.Startdatum] = {"<=$(=Date(Today()))"},
                [Darlehen_Stammdaten.Enddatum] = {">$(=Date(Today()))"}>}
                [Darlehen_Stammdaten.Startdatum],
                -[Darlehen_Stammdaten.Startdatum]
            )
        ) / 365.25,
        ObjektKey
    )
)

2. LoanDurationLeft:
Avg(
    Aggr(
        (Today() -
            FirstSortedValue(
                {<[Darlehen_Stammdaten.Startdatum] = {"<=$(=Date(Today()))"},
                [Darlehen_Stammdaten.Enddatum] = {">$(=Date(Today()))"}>}
                [Darlehen_Stammdaten.Enddatum],
                -[Darlehen_Stammdaten.Startdatum]
            )
        ) / 365.25,
        ObjektKey
    )
)

My Problem:

I tried to create a piechart with the dimension:
=ValueList('DimensionPassed', 'DimensionLeft')

and the measure:
If(ValueList('DimensionPassed', 'DimensionLeft') = 'DimensionPassed', LoanDurationPassed, LoanDurationLeft)

Whatever I do and try, it always just keeps showing LoanDurationPassed. Where is my mistake? What is my error in reasoning? I would be super thankful for all your ideas and help! If you got further questions regarding the topic, please hit me up. Thank you all in advance!!

[rubenmarin · Accepted solution]

Hi, the scrennshot shows If(ValueList('DimensionPassed', 'DimensionLeft') = 'Dimension-Passed'

Maybe that extra dash is causing the issue.

You can also try setting the LoanDurationPassed and LoanDurationLeft as different measures in a table, to confirm it returns the expected values.

Also note that you can use script to precalculate the duration of each loan, so you can end with a simpler Avg(LoanDurationPassed) and Avg(LoanDurationLeft) expressions, but if the data is not too large, and the curent expression works, there is no need to do that.

[Anil_Babu_Samineni]

I don't see your second parameter which you want to pass? An example,

If(ValueList('DimensionPassed', 'DimensionLeft') = 'DimensionPassed', LoanDurationPassed, If(ValueList('DimensionPassed', 'DimensionLeft') = 'DimensionLeft', LoanDurationLeft))