Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
zhaohaifeng
New 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

7 Replies

Re: Difference between straight table and pivot table

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

zhaohaifeng
New Contributor III

Re: Difference between straight table and pivot table

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
Valued Contributor

Re: Difference between straight table and pivot table

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.

Highlighted
zhaohaifeng
New Contributor III

Re: Difference between straight table and pivot table

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
Valued Contributor

Re: Difference between straight table and pivot table

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
New Contributor III

Re: Difference between straight table and pivot table

 

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
Valued Contributor

Re: Difference between straight table and pivot table

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.