Qlik Sense Pivot Table Set Analysis Help

[adamh2404]

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

[lironbaram]

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]

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]

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.

[felipedl]

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

Can you share the app?

[lironbaram]

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]

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]

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]

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