How to create comparison sales current and last year with restriction?

[#1]

Hi all,

i'm still learning Qlik and i got this issue to handle, bottom line is i have to create top gain & loss sales with min value (like above 100 mio on top gain and 50 mio on top loss, so the data will shown up above 100 & 50 mio only)

So the detail is, i have to compare between CY and LY which one is the best sales on CY above 100 mio sales for Top Gain and for Top Loss,  compare between CY and LY which one is the most poor sales on LY  but show data above 50 mio sales, i've been try using Aggr function and choose dimension limit for restriction but its not shown up properly, Anyone ever face this issue before?

Thanks a lot before,

Cheers,

[#2]

Hi,

Use Set Analysis, can u pls share sample data.

@Sub2u

[#3]

Pls refer below link.

Set Analysis: syntaxes, examples

[#4]

Hi Sub2u,

Sorry bro, its 1 AM in here too tired to make it one, but i'll share the script,

Top gain: Max({<Year={$(=Only(Year))}>}aggr(sum({<Year={$(=Only(Year))}>} Amount), Customer)) this is for expression CY, and you put -1 for LY, then for the restriction value 100 mio, i sett in dimension limit, choose show only value that accumulate to ..

Top loss: same expression but with Min, Min({<Year={$(=Only(Year))}>}aggr(sum({<Year={$(=Only(Year))}>} Amount), Customer)) and so on.. but this set analysis doesn't work,

Thanks for stopping by,

cheers,

[#5]

Anyone got this answer for this issue? Thanks a lot,

Anyway, Happy holiday all

[ecolomer]

Can you upload qv file?

[#7]

Hi Enrique,

Here's the dummy qv that i try, still doesnt work, but maybe you can help where's the issue on the script, many thanks before..

Cheers,

[#8]

Can anyone help me on this? Thanks.. oh btw happy new year All

Cheers,