Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

adamh2404
New Contributor II

Qlik Sense Pivot Table Set Analysis Help

Hello,

I have an expression that works perfectly fine when selecting one dimension value as I am using the ONLY function like so:

Sum({<[Sales Invoice Date]={">=$(=max(Drop_Date)) <=$(=vDropDateMonthEnd)"},Product_Brochure_Code={$(=only([Brochure_Code]))}>}[Sales Total (GBP)])

However I now need to put this data into a pivot table so that I can compare [Brochure_Code]'s against each other, like in the first attached image, previously done manually in Excel.

Is it possible to use a pivot tables column/dimension value inside of the set analysis expression?

Current Qlik pivot layout in second attachment.

Any help is appreciated.

Thanks

Adam

8 Replies
lironbaram
Honored Contributor II

Re: Qlik Sense Pivot Table Set Analysis Help

hi

if product Product_Brochure_Code is  a dimension in the table than you don't want to use it in the set analysis

the expression

Sum({<[Sales Invoice Date]={">=$(=max(Drop_Date)) <=$(=vDropDateMonthEnd)"}>}[Sales Total (GBP)])

should work for you ,

your current expression only support one value of the field because of the only function

felipedl
Valued Contributor III

Re: Qlik Sense Pivot Table Set Analysis Help

Hi Adam,

Might be a long stretch without seeing the data, but you could take the Product_Brochure_Code out of the set analysis and add it as a dimensuion of your pivot table, getting the sum by it and the other dimensions, without setting a particular code in the set.

Felipe.

adamh2404
New Contributor II

Re: Qlik Sense Pivot Table Set Analysis Help

Adding Product_Brochure_Code as a dimension and removing it from the set analysis does not give me the correct result due to the way the data is modelled - see attached.

Capture3.PNG

felipedl
Valued Contributor III

Re: Qlik Sense Pivot Table Set Analysis Help

Whats the result on the pivot table when you add the dimension and take out the set?

Can you share the app?

lironbaram
Honored Contributor II

Re: Qlik Sense Pivot Table Set Analysis Help

hi

according to your scheme , it should work

as there seems to be a direct connection from Brochoure_products table to the Sales table

and those are the only one involved in the dimension and expression in the table

adamh2404
New Contributor II

Re: Qlik Sense Pivot Table Set Analysis Help

Hi,

Sorry can't share the app due to customer/sales info contained within.

For one selected brochure I'm working with, the expression should return £86,131. However, when doing the suggested action in the pivot it returns £1,263,980....

I believe it is to with using the only function across 2 tables before, where as just putting one dimension in the pivot is not following that same logic.

Product_Brochure_Code={$(=only([Brochure_Code]))} - Product_Brochure_Code and Brochure_Code are in different tables in the model.

lironbaram
Honored Contributor II

Re: Qlik Sense Pivot Table Set Analysis Help

hi

ok so you can try this it might work

Sum({<[Sales Invoice Date]={">=$(=max(Drop_Date)) <=$(=vDropDateMonthEnd)"}>} if(Product_Brochure_Code=[Brochure_Code],Sales Total (GBP)]))


adamh2404
New Contributor II

Re: Qlik Sense Pivot Table Set Analysis Help

That takes it down to £103,597 but still not quite right.

Community Browser