Match two different data tables in a graph

[Luisanabria]

Hi all! 😄 

Im working in a new proyect to show the point of breakeven of one invesment like this:

So l have 2 tables 

First table has the data of all my Leads that we got in a Year (ID, name, contact..)

Second table has investment amount by month 

Is possible to get a graph like the image? Doing a relationship of the Count of Leads by month, and the amount by month?

[RafaelBarrios]

Hi @Luisanabria 

I don’t think with standard charts you can, but close, maybe if you paste a couple of records of your data I can be more precise.

You can try scatter plot 

https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Visualizations/Scatter...

where you can use a measure for X and another for Y

Let us now how it goes

best,

[Oleg_Troyansky]

Hi Fernando,

It's possible to create a chart like this, if the X-axis dimension is driven by the numbers that are associated with months, and the Y-axis shows numbers that are associated to the same months. The important part is that the Month field has to be associated to both numbers - you might need to do some data modeling to achieve that.

It's a bit advanced though, and requires some advanced techniques. I'll list some of them for you:

1. For the X-axis, you will need to create a calculated dimension with the use of the function AGGR(). Something like this should do the job:

AGGR(sum(QtyX), YearMonth)

2. It looks like you want your numbers on both axes to be cumulative. If that's the case, you will need to use additional functions like Rangesum() and Above(). So, the X dimension might have to look like this:

AGGR(Rangesum(Above(sum(QtyX), 0, RowNo()))      , YearMonth)

3. In the Y-axis the Measure doesn't need to be enclosed in the AGGR() function, it could be as simple as this:

sum(QtyY)

or like this, if you want it to be cumulative:

Rangesum(Above(sum(QtyY), 0, RowNo()))

I teach similar techniques in my session on advanced Set Analysis and AGGR at the Masters Summit for Qlik, which is coming to New Orleans on November 14th. Check out our agenda and see if you can attend. You will learn a lot of cool techniques!