Formula in Pivot works in Qlikview but not in Sense

[janvandernagel]

I want to expres the incidents per 1000.000 km.

The set expression is working fine in Qlik View, but in Qlik Sense  I don't get a result.

Set Expression for column 'Score':

=Incidents/(Sum( TOTAL<Jaar,Kwartaal> {$<Categorie, Type, Koppeltype>} Km) /1000000)

Qlikview

Qlik Sense

Why is it  not working in Sense?

Thanks in advance

Jan

[justISO]

Hi, I would guess QlikView aggregation is not the same as in QlikSense. If I understand your formula correctly, and there are aggregation on Jaar and Kwartaal and ignoring what is selected in Categorie, Type, Koppeltype fields, I would try this:

sum(Incidents) / (Aggr(nodistinct sum({$<Categorie=, Type=, Koppeltype= >} Km), Jaar,Kwartaal) /1000000)

[janvandernagel]

Thanks I'm getting a result now. Only when  I expand all columns I get the right km.

But when I colapse the Year (Jaar) then the result doesn't make sense:

For testing I only used this part( Aggr(nodistinct sum({$<Categorie=, Type=, Koppeltype= >} Km), Jaar,Kwartaal) /1000000)

Per quarter is ok:

But per Year is not:

Any suggestions?

[justISO]

maybe sum(Aggr(...  Not sure actually. Without sample of your data it is hard to say what actual result it should be.

[janvandernagel]

Sum was not working.

I'm not shure how to provide a sample of our data, there are quite a lot of tables.

Mayby by reading the next part you get an idea of what is going wrong?

The km's per quarter are:

Q1   3.68
Q2   4.02
Q3   4.02
Q4   4.29

The totals per quarter are correct in the pivot. But once collapsed the Yeartotal should be 16.01.  But in the pivot you will only see one of the quarter km's  from above or just nothing?

[justISO]

So, let's back to understanding what your formula should calculate? Because, sorry, I'm not familiar with QlikView, so I don't feel confident to understand your original formula.

As I understand you take sum of incidents and divide by sum of Km and once again by 1000000. If yes, basically formula sum(Incidents)/sum(Km)/1000000 should be enough.

[janvandernagel]

I want to expres the number of incidents per 1.000.000 kilometer per Year and per Quarter.

The km should the total km's of the year or quarter in the pivot, so disregarding the Category and Type of incident.

Your first suggestion ((Aggr(nodistinct sum({$<Categorie=, Type=, Koppeltype= >} Km), Jaar,Kwartaal) /1000000) is working for the km's per quarter, but not per year.