topic Re: Sets Analysis Query in QlikView

Sets Analysis Query

shyamcharan — Wed, 12 Nov 2014 02:27:36 GMT

Hi,

Can anyone please explain what point I miss in understanding the difference between '1' and '$' Identifier.

Please find the attached QVW document with a selection on the company.

I am expecting the Sum({$<Company = {'A','B'}, Year = {2011}>} Sales) value to be 2 rather than 8.

Thanks heaps in advance.

Regards,

Shyam.

Re: Sets Analysis Query

Wed, 12 Nov 2014 02:48:57 GMT

year:

2000

2001

if u write $ and u click of 2000 then it is going to show sum(sales) for 2000.

if u write 1 and u click on any year 2000,20001 doesn't mater it returns total sum(sales) (i.e is 2000+ 20001 sales)

Re: Sets Analysis Query

shyamcharan — Wed, 12 Nov 2014 02:52:48 GMT

Thanks Manoj.

But thats not what happening in the attached QVW.

Can you please check that and let me know your observations.

Thanks again for the response.

Re: Sets Analysis Query

Wed, 12 Nov 2014 03:03:44 GMT

Sum({$<Company = {'B'}, Year = {2011}>} Sales) -- you will get output 2 here

if u write including (A,B) then u r getting sum(sales) for 2011 . if u select A OR B u get sum(sales) for 2011

=Sum({1<Company = {'A','B'}, Year = {2011}>} Sales) = 8 u will get here

Re: Sets Analysis Query

danieloberbilli — Wed, 12 Nov 2014 03:05:08 GMT

There is no difference in this particular example as you consider all available fields within your set analysis.

Bu try out to create another field like Category (first two rows Cat1, last two rows Cat2) - and don't mention this new field in your set analysis...then you will see the difference: as Category is not considered - any Category selection is ignored in the set analysis with {1} but considered in the one below with {$}

Re: Sets Analysis Query

aveeeeeee7en — Wed, 12 Nov 2014 03:21:24 GMT

This is Conceptual.

See this:

Year is 2011: Sales: A=6 + B=2 = 8

The difference between '1' & '$' is
'1' - It will Freeze the value which means the Value '8' will not change on any Selection.
'$' -The value will change as per the Selection.

Also, check the Attachment.

Regards

Av7eN

Re: Sets Analysis Query

Wed, 12 Nov 2014 03:33:34 GMT

why are you expecting 2 here? because in your data set in 2011, A=6 and B=2 then in set analysis {'A','B'}= 8

Re: Sets Analysis Query

shyamcharan — Wed, 12 Nov 2014 03:36:52 GMT

Thanks Daniel.

That clarifies my doubt but, why the Company = {'A','B'} set has no effect on the result even when it is in the set analysis.

When I select a Category and make a selection on it like '1' then I was expecting a result as '8' because I have 'A' and 'B' in the set. How is this excluded when I select the Category?

Regards,

Shyam.

Re: Sets Analysis Query

Wed, 12 Nov 2014 03:40:54 GMT

if(match(Category,'A'.'B'),sum(sales))

Re: Sets Analysis Query

danieloberbilli — Wed, 12 Nov 2014 03:43:33 GMT

if you explicitly write Company=A,B in your set analysis - QlikView will always consider this selection. The {1} effects all (other) field selections that are not explicitly defined within your set analysis expression

Re: Sets Analysis Query

shyamcharan — Wed, 12 Nov 2014 03:44:14 GMT

Hi Dushan,

Yes you are correct.

But when I make a selection on the company and select A or B i should get 6 or 2 but not 8 correct?

Please correct me wherever required.

Re: Sets Analysis Query

danieloberbilli — Wed, 12 Nov 2014 03:45:14 GMT

additional: the Company=A,B, has an effect...its infact the effect why you don't get the 2 but the 8 instead...QlikView selects A AND B

Re: Sets Analysis Query

Wed, 12 Nov 2014 03:48:09 GMT

Hi Shyam,

Then you have to change your expression like this

=Sum({<Year = {2011}>} Sales)

Re: Sets Analysis Query

Wed, 12 Nov 2014 03:52:05 GMT

sum({<year={2011}>}sales) u will get

go like this and check it

Sum({1<Company = {'A','B'}, Year = {2011}>} Sales)

Re: Sets Analysis Query

Wed, 12 Nov 2014 03:52:43 GMT

sum({<year={2011}>}sales) u will get for indidual

go like this and check it

Sum({1<Company = {'A','B'}, Year = {2011}>} Sales) for 8

Re: Re: Sets Analysis Query

shyamcharan — Wed, 12 Nov 2014 03:53:12 GMT

Thanks Daniel.

So from what I did so far,

I have Company ={'A','B'}.Year={2011} in the set and I have made a selection on the Category list box as Category=1. Then I can see the change.

But wondering now why the Company values in the sets is excluded.

Regards,

Shyam.

Re: Re: Sets Analysis Query

danieloberbilli — Wed, 12 Nov 2014 03:59:44 GMT

Beware that what you see in white/grey/green in the qlikview listboxes do not always have an effect on all your calculations...as I said, if you explicitly use these fields in Set Analysis - every user selection by Listbox might ignored (depending on what you define in set analysis)