Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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.
Whats the result on the pivot table when you add the dimension and take out the set?
Can you share the app?
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
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.
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)]))
That takes it down to £103,597 but still not quite right.