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

Difference between straight table and pivot table

Hi Guys,

 

Now we faced the challenge as below, for the same dimension expression and same measure expression in straight table and pivot table, the measure value was not not matched. The straight table value was correct, Do you know any differnt between them? Also any advice how to check the issue here? Thanks in advance.

Straight and Pivot.PNG

Best Regards,

Haifeng

Labels (1)
7 Replies
avinashelite

but as per the image both the expression not same rite ? could you please confirm the same 

zhaohaifeng
Contributor III
Contributor III
Author

Hi Man,

 

The expression was same, what you saw different was the variable expend in the pivot table.

Do you have any experience on this case ? Thanks in advance.

 

Best Regards,

Haifeng

timpoismans
Specialist
Specialist

I'd try to use the exact same measure, thus without using the variable, and see if the issue persists. If it doesn't, the issue is the variable (the measure in it). If it does persist, we can look further into it.

zhaohaifeng
Contributor III
Contributor III
Author

Yes, we did that, but it also dont have the same value.

Now our dimension was like 

Pick(match(
GetFieldSelections(Filter),'A','B','C'
),
FieldAA,FieldAB,FieldAC
)

And Measure was like 

Pick(match(getfieldselection(Filter),'A','B','C'),sum(Avalue),sum(Bvalue),sum(Cvalue)).

The field AA and AB are in the data model, AC not association with the data model, is it the root cause? Can you share some experience? Thanks

Best Regards,

Haifeng

timpoismans
Specialist
Specialist

Hi there

Could you possibly share an app with sample data? It would be a lot easier for us to help you if we can see what's happening and can change the measures instead of going back and forth with you on what might be the problem.

zhaohaifeng
Contributor III
Contributor III
Author

 

Thanks very much.

 

I attached the sample data, as you can see, when select A or B, the value was not same, the pivot table was not correct. Can you help to check? Thanks.

 

Best Regards,

Haifeng

 

timpoismans
Specialist
Specialist

The problem is your table with the AC values. It's a different level of data (each value in AC represents two values in AA).

On top of that, using the dimension as it is defined, in a pivot table, it causes issues as it looks at your AC-values being matched to each AA-value.
Which is why in your sample, the values appear as if they are trippled in the pivot table (As AC has three values).
If you'd test a pivot table with the same formula, but with the dimensions AA or AB, the result would match your straight table.

Is there a reason why you need to use a pivot table and a straight table won't suffice?

 

A fix to show the right results in your pivot table would be a slight alteration to the measure.
I've uploaded your sample app again, but the measure edited, so you can test if it works as it should.