topic Re: Calculated dimension in QlikView

Calculated dimension

brindlogcool — Wed, 28 Oct 2015 20:17:02 GMT

I have the data source as

id , name , value

1 ,cd,23

2,aa,24

2,aa,25

3,bb,1

3,bb,2

4,ee,3

5,ff,4

i have the condition as aa>24 and bb>1 and i need the table to show as below

id , name , value

1 ,cd,23

2,aa,25

3,bb,2

4,ee,3

5,ff,4

How to implement it?

Re: Calculated dimension

chancekbarkley — Wed, 28 Oct 2015 20:29:34 GMT

I'm not clear on your business logic, but it appears that you're looking for the max value for each ID/Name combination?

In that case you could use a group by and do something like this:

Load id, name, MAX(value)

Resident <table name>

Group By id, name;

That should provide the result set you're looking for in this particular case.

-cb

Re: Calculated dimension

brindlogcool — Wed, 28 Oct 2015 20:57:47 GMT

thanks cb, May be i didn't put the question in the right way.

I want to implement it at the UI level. User will define the filter condition as aa>24 . And the table will all the rows as is except for the aa ( Will show only one row for aa 25).

id , name , value

1 ,cd,23

2,aa,25

3,bb,2

4,ee,3

5,ff,4

Re: Calculated dimension

chancekbarkley — Wed, 28 Oct 2015 21:12:52 GMT

This probably isn't the answer you're looking for, but I'm multitasking and this is the best I can come up with at the moment - if you're using a list box, you can click search(or just click in the header and start typing) and type a filter condition like name='aa' and Value>24.

You can also use aggregation functions in the search box in the UI as in SUM(Value)>24.

Of course there is always set analysis as well, it just depends on how dynamic it needs to be.

Re: Calculated dimension

brindlogcool — Wed, 28 Oct 2015 21:41:29 GMT

it is more dynamic meaning that user want to see all the rows and also restrict one product with specific criteria.Any suggestions on how to implement it

Re: Calculated dimension

MarcoWedel — Wed, 28 Oct 2015 22:38:26 GMT

Hi,

if you specifically want your users to enter a filter condition like 'aa>24' then one solution could be:

=Sum({$-$<name={"$(=SubField(vSelStr,'>',1))"}>+$<name={"$(=SubField(vSelStr,'>',1))"}, value={">$(=SubField(vSelStr,'>',2))"}>} 1)

hope this helps

regards

Marco

Re: Calculated dimension

chancekbarkley — Thu, 29 Oct 2015 11:47:22 GMT

What about a table box with an input field to set the min value, then a straight table to show the list using a calculated dimension for the value? Example attached.

1. Hover over the field and click the carrot to enter edit mode:

2. Change the value to the minimum you desire for that ID/Name combination:

3. Hit enter and the filter updates the straight table to the right. Note that AA only has a single value:

I haven't thought through how to handle the extra column. I set the expression equal to 1, put a space in the label, and changed it to a LED chart so it would be empty. I'm sure there is a better way to handle it, but if all you want is a list based on user filter criteria, maybe this will do it.

-CB

Re: Calculated dimension

brindlogcool — Thu, 29 Oct 2015 16:42:30 GMT

Thanks CB,

Its good solution to.