topic Re: Help with Set Analysis in QlikView

Help with Set Analysis

Mon, 20 Oct 2014 16:22:25 GMT

Hello I have a table "Sales' that I am filtering using 'Set Analysis' and I would like to add another column that shows me the Total % per line. I know how to do this with when using simple operations and no restrictions on the data but I don't know how to get there when restricting the results. For example on my table "how to Replicate this' I have 2 columns: Sales and %, where sales =Sum(QTY) and %=Sum(QTY)/Sum(TOTAL<Year>QTY). Because my 'Sales' table has restrictions on the data I am unable to show the %; I tried using the Column Label but I couldn't get it to work: $(='[Sales])/Sum(TOTAL<Year>$(='[Sales])). Can anybody help please?

I am attaching a sample qvw file.

Thank you,

Hugo

Re: Help with Set Analysis

stigchel — Mon, 20 Oct 2014 16:30:38 GMT

See attached your example edited

Re: Help with Set Analysis

JonnyPoole — Mon, 20 Oct 2014 16:32:29 GMT

Is this what you are looking for ?

i wasn't sure what you wanted to do with the Code field... it has a set modifier which prevents seeing Code=1.

I removed it for the example on the top left and incldued it on the right

hopefully one of them is what you are looking for.

ps: just use TOTAL in the denominator and not in the top.

Re: Help with Set Analysis

Mon, 20 Oct 2014 19:13:28 GMT

Thank you Piet and Jonathan for your help, both answers are very helpful but it was Jonathan's answer the one that gave me the 100% correct answer, because in the case when filtering out records, the % must reflect this on the results and by placing the entire expression after the TOTAL, the result is adjusted accordingly.

I really appreciate your help, I tried different ways for about 20 hours and I couldn't get it to work.