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: 
MPkg
Contributor III
Contributor III

How to create a Line Chart formula and Fields in IF statement in Qlik Sense

I need to create a line chart where:

- Dimension is Time (0-10s, step 1s)

- Measure is  Velocity (changes in time).

I have two tables of these two values, in the first table, I have the Time_1 and Velocity_1 fields, and in the second table Time_2 and Velocity_2 fields.

By selecting the value, in a separate table, in the Vehicle field (Vehicle_1 or Vehicle_2) the chart should automatically change.

The problem is that when I include two fields in the chart formula the chart gets in an endless loading state and does not render.

 

The formula in the chart is:

for Dimension:

 

if(GetSelectedCount([Vehicle])=1,If([Vehicle]='Vehicle_1',Time_1,If([Vehicle]='Vehicle_2',Time_2,0)))

 

for Measure:

 

if(GetSelectedCount([Vehicle])=1,If([Vehicle]='Vehicle_1',Velocity_1,If([Vehicle]='Vehicle_2',Velocity_2,0)))

 

 

Note:

The next formula works:

 

if(GetSelectedCount([Vehicle])=1,If([Vehicle]='Vehicle_1',Velocity_1,If([Vehicle]='Vehicle_2',5,0)))

 

I tried nonworking formula in Multi KPI like this:

 

if(GetSelectedCount([Vehicle])=1,If([Vehicle]='Vehicle_1','Velocity_1',If([Vehicle]='Vehicle_2','Velocity_2',0)))

 

 and with strings, everything works nicely, but when I use Fields instead of strings there is a bug.

Labels (1)
2 Solutions

Accepted Solutions
vinieme12
Champion III
Champion III

The solution suggested by @stevedark is the best option, if possible just concatenate the datasets 

A front-end only alternative would be to use "Show Column If" to show or hide a column conditionally 

Example

Add Dimensions 

1) Time_1

Show columnif expression = if(GetSelectedCount([Vehicle])=1 and [Vehicle]='Vehicle_1',1,0)

2) Time_2

Show columnif expression = if(GetSelectedCount([Vehicle])=1 and [Vehicle]='Vehicle_2',1,0) 

 

Add Measures

1) sum(Velocity_1)

Show columnif expression = if(GetSelectedCount([Vehicle])=1 and [Vehicle]='Vehicle_1',1,0)

2) sum(Velocity_2)

Show columnif expression = if(GetSelectedCount([Vehicle])=1 and [Vehicle]='Vehicle_2',1,0)  

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @MPkg 

Have you tried the concatenation approach? If you have two tables that are not connected (or tenuously connected) in the data model, referring to both of them in one chart is going to cause problems.

Steve

View solution in original post

11 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @MPkg 

It might be helpful if you can post an app or some screenshots demonstrating your issue.

From what I understand though, it sounds like you need to concatenate your two tables. Presently if they are both in the data model but are not joined then there will be a Cartesian product - which is always a bad thing.

Something like:

Velocity:
LOAD
  'Vehicle1' as Vehicle,
  Time,
  Velocity
FROM [lib://DataFiles/Vehicle1.csv] (txt);

CONCATENATE(Velocity)
LOAD
   'Vechicle2' as Vehicle,
   Time,
   Velocity
FROM [lib://DataFiles/Vehicle2.csv] (txt);

 

Once you have everything in a single table life becomes simple.

You can have a line chart with two dimensions, Time and Vehcile and an measure of avg(Velocity). That may only be an average of one data point at any point, but you should always have an aggregation in your measure.

Hope that helps.

Steve

 

vinieme12
Champion III
Champion III

The solution suggested by @stevedark is the best option, if possible just concatenate the datasets 

A front-end only alternative would be to use "Show Column If" to show or hide a column conditionally 

Example

Add Dimensions 

1) Time_1

Show columnif expression = if(GetSelectedCount([Vehicle])=1 and [Vehicle]='Vehicle_1',1,0)

2) Time_2

Show columnif expression = if(GetSelectedCount([Vehicle])=1 and [Vehicle]='Vehicle_2',1,0) 

 

Add Measures

1) sum(Velocity_1)

Show columnif expression = if(GetSelectedCount([Vehicle])=1 and [Vehicle]='Vehicle_1',1,0)

2) sum(Velocity_2)

Show columnif expression = if(GetSelectedCount([Vehicle])=1 and [Vehicle]='Vehicle_2',1,0)  

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
MPkg
Contributor III
Contributor III
Author

Hello,

Thanks both of you for the reply.

Using the ShowHide Container I have solved this problem, making the chart visible based on selection of the Vehicle. Only trouble with this is that it is not fully automatic. I need to create master item for every chart that I am supposed to use, and I am not sure if this will be accepted.

 

Still I do not understand why if statement does not work.

I am not able to get all data into one table because of the amount of the data.

 

This is what happens:

This is velocity in time:

MPkg_0-1653900340329.png

The formulas for dimension and measure used is this:

 

time:
if(GetSelectedCount([Vehicle])=1,If([Vehicle]='Vehicle_1',Time_1,If([Vehicle]='Vehicle_2',0,0)))

velocity:
if(GetSelectedCount([Vehicle])=1,If([Vehicle]='Vehicle_1',Velocity_1,If([Vehicle]='Vehicle_2',0,0)))

 

 

For the second chart it is this:

MPkg_1-1653900620409.png

the formulas used are:

 

time:
if(GetSelectedCount([Vehicle])=1,If([Vehicle]='Vehicle_1',0,If([Vehicle]='Vehicle_2',Time_2,0)))

velocity:
if(GetSelectedCount([Vehicle])=1,If([Vehicle]='Vehicle_1',0,If([Vehicle]='Vehicle_2',Velocity_2,0)))

 

 

But when I use both fields in the formula I get this:

MPkg_2-1653901031921.png

and the chart never opens.

formula:

 

time:
if(GetSelectedCount([Vehicle])=1,If([Vehicle]='Vehicle_1',Time_1,If([Vehicle]='Vehicle_2',Time_2,0)))

velocity:
if(GetSelectedCount([Vehicle])=1,If([Vehicle]='Vehicle_1',Velocity_1,If([Vehicle]='Vehicle_2',Velocity_2,0)))

 

EDIT:

After some time this shows up:

MPkg_3-1653902317317.png

I do not know why is this when I am not trying to load both charts at once.

 It would be very nice if I could do this without master items.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @MPkg 

Have you tried the concatenation approach? If you have two tables that are not connected (or tenuously connected) in the data model, referring to both of them in one chart is going to cause problems.

Steve

vinieme12
Champion III
Champion III

You Can Also 

Option1:  Use Variables

Define below variables

vDimension

 

=if(GetSelectedCount([Vehicle])=1 ,pick(wildmatch([Vehicle],'Vehicle_1','Vehicle_2'),'Time_1','Time_2')
)

 

vMeasure

 

=if(GetSelectedCount([Vehicle])=1 ,pick(wildmatch([Vehicle],'Vehicle_1','Vehicle_2'),'Velocity_1','Velocity_2')
)

 

 

In Chart, just use

Dimension

=$(vDimension)

Measure

=SUM($(vMeasure))

 

Option 2:  Use a Container, 

https://www.youtube.com/watch?v=IA1CTJZXeXM

Create two charts, and place them in a container for users to toggle between

first chart

Dimension

Time_1

Measure

sum(Velocity_1)

Second chart

Dimension

Time_2

Measure

sum(Velocity_2)

1)Always wrap measures in an aggregation function

2) If() block is slower in dimension as it will evaluate for each dimension value

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
MPkg
Contributor III
Contributor III
Author

@stevedark Yes, I have tried concatenation approach, and it works correctly, but it defines different approach than I wanted to. Thank you very much, if I do not find a way with two fields in an if loop, I must do something like this, or use the Container (Show/Hide or just Container).

 

@vinieme12 I have already tried this approach with variables, but it does not work. It does the same thing as the whole function.

Second option would work as well as the Show/Hide, but as I have seen I do not need to create master item, and that is great!

note:

It was the same for me if I choose aggregation or not. It works just fine without the aggregation factor.

Thanks both of you once again! You helped me a lot!

It just seems that Qlik has a problem with importing more than one field in Combo Box Dimension or Measure because it imports all data from a formula to a chart, and then implements the conditions from that formula, and this creates lack of some kind of memory.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @MPkg 

You could probably do some kind of blend of the two approaches. The concatenation will be required to get the data model not to foul up. You could then use set analysis based on another filter pane to chose which rows appear on the chart.

If you had a data island of VehicleChoice with Vehicle1 and Vehicle2 in you could then have a single expression in the chart with set analysis:

sum({<Vehicle*={'$(=minstring(VehicleChoice))'}>}Value)

Hope that helps move you a little further forward.

Steve

MPkg
Contributor III
Contributor III
Author

Hello @stevedark ,

Could you please explain a little bit this set expression formula.

If I understand it correctly Vehicle* will take the value of minstring(VehicleChoice), which is the only one value left in this field by some selections.

Is the VehicleChoice a field in data island?

How do I implement set analysis in the chart? I am not sure how to use this expression.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @MPkg 

The * after the vehicle is actually part of the set analysis comparison *= rather than a wildcard on the end of Vehicle. It means that if someone makes a selection on the Vehicle field that will still be taken into account. See this post for details:
https://www.quickintelligence.co.uk/qlik-set-analysis-star-equals/

With the set analysis only Velocity where the value in the Vehicle field equals the minimum value from the VehicleChoice field. This may be the only selected value in that field, but if no selection is made then it will be the first. If you wanted it to be on a match of all selected (or the whole list if nothing selected) you would have:

sum({<Vehicle*=P(VehicleChoice)>}Value)

To be honest though, there would be no point in doing this, as you just as well not have the VehicleChoice field and just have a filter on Vehicle.

To use the expression you just put the expression with the set in the Measure of the chart.

To create the VehicleChoice data island you can just insert this in your load script:

VehicleChoice:
LOAD
   VehicleChoice
INLINE [
VehicleChoice
Vehicle1
Vehicle2
];

Hope that all helps?

Steve