topic Set Analysis inside of Set analysis in QlikView

Set Analysis inside of Set analysis

Anonymous — Tue, 12 Dec 2017 19:36:42 GMT

Need some help to figure out a set inside another set. I thought my expression will work in straight table

sum({<Taxable={1}, TaxType={'03'}, Group={'0000'}, Item ={'000'}, Effective={"=$(MAx({<Taxable={1}, Group={ '0000'}, Item ={ '000'}, TaxType={'03'}>} Effective))"}>} distinct TaxRate)

If I replace my set analysis inside set to find the max date with a constant then expression is working correctly. Any help is appreciated to solve this.

Re: Set Analysis inside of Set analysis

sunny_talwar — Tue, 12 Dec 2017 19:58:43 GMT

Try this

Sum({<Taxable={1}, TaxType={'03'}, Group={'0000'}, Item ={'000'}, Effective={"$(=Max({<Taxable={1}, Group={ '0000'}, Item ={ '000'}, TaxType={'03'}>} Effective))"}>} DISTINCT TaxRate)

UPDATE: You need the equal sign within the dollar sign expansion and not outside of it

Re: Set Analysis inside of Set analysis

Anonymous — Wed, 13 Dec 2017 01:57:55 GMT

I tried and not working. Any other mistake that you can see in it ?

Re: Set Analysis inside of Set analysis

pradosh_thakur — Wed, 13 Dec 2017 03:55:54 GMT

Is effective a date field ?

if not then do you have effective as a dimension in the straight table ?

If yes then paste this Max({<Taxable={1}, Group={ '0000'}, Item ={ '000'}, TaxType={'03'}>} Effective) in a text box and see what value you are getting. may be you have to use date function.

regards

Pradosh

Re: Set Analysis inside of Set analysis

devarasu07 — Wed, 13 Dec 2017 04:01:32 GMT

Hi,

Can you try single quote instead of double quote, can you share your mock app with sample data, that would be easy for us to check and help u.

Sum({$<Taxable={1}, TaxType={'03'}, Group={'0000'}, Item ={'000'}, Effective = {'$(=max({$<Taxable={1}, Group={'0000'}, Item ={'000'}, TaxType={'03'}>} Effective))'} >} DISTINCT TaxRate)

Sum({$<Taxable={1}, TaxType={'03'}, Group={'0000'}, Item ={'000'}, Effective = {'$(=max(Effective))'} >} DISTINCT TaxRate)

there field Effective is numeric data or date or string value?

Thanks,

Deva

Re: Set Analysis inside of Set analysis

Anonymous — Wed, 13 Dec 2017 04:06:46 GMT

Effective is not a Date field and can't have that in dimension. I modified like this now

sum({<Taxable={1}, TaxType={ '01'}, Group={'0000'}, Item ={'000'},Effective={"<= $(=MAx({<Taxable={1}, Group={ '0000'}, Item ={ '000'}, TaxType={'01'}>} Effective))>}"}>} Distinct TaxRate)

The idea to get only the one tax rates based on highest effective date in a set. But what I can observe now is that this is pulling all the records in the set not the max date record.

Re: Set Analysis inside of Set analysis

Anonymous — Wed, 13 Dec 2017 04:13:54 GMT

I tried to use single quote and the results are same , Chart pick up all records on calculation regardless max effective date.

Re: Set Analysis inside of Set analysis

Anonymous — Wed, 13 Dec 2017 04:26:03 GMT

Y u are mentioning the <= in Effective ={

Remove it

sum({<Taxable={1}, TaxType={ '01'}, Group={'0000'}, Item ={'000'},Effective={"<= $(=MAx({<Taxable={1}, Group={ '0000'}, Item ={ '000'}, TaxType={'01'}>} Effective))>}"}>} Distinct TaxRate)

Re: Set Analysis inside of Set analysis

jonathandienst — Wed, 13 Dec 2017 04:47:15 GMT

>>Effective is not a Date field

So its a text field? Then the Max() will not work properly. Convert Effective to a date field when loading for best results here.

Re: Set Analysis inside of Set analysis

Anonymous — Wed, 13 Dec 2017 06:35:41 GMT

Thanks , if I remove it then results turns to zero.

Re: Set Analysis inside of Set analysis

pradosh_thakur — Wed, 13 Dec 2017 06:41:41 GMT

Try this

I have deleted the space. it should work.

sum({<Taxable={1}, TaxType={ '01'}, Group={'0000'}, Item ={'000'},Effective={"<=$(=MAx({<Taxable={1}, Group={ '0000'}, Item ={ '000'},TaxType={'01'}>} Effective))>}"}>}Distinct TaxRate)

regards

Pradosh

Re: Set Analysis inside of Set analysis

its_anandrjs — Wed, 13 Dec 2017 06:48:12 GMT

Try to write another variable and do this with variable

Let vTaxVariable = 'Taxable={1}, Group={ '0000'}, Item ={ '000'}, TaxType={'01'}';

=Sum( {<Taxable ={1}, TaxType ={'01'}, Group ={'0000'}, Item ={'000'}, Effective ={"<=$(=Max({< $(vTaxVariable)>} Effective))>}"} >} Distinct TaxRate)

Re: Set Analysis inside of Set analysis

Anonymous — Wed, 13 Dec 2017 06:54:56 GMT

If I do in script num(Effective) will convert numeric ?, I put same in the chart and not doing properly. What I found that when I use >= or <= Effective values are coming but compare '=' then it turns to 0.

Re: Set Analysis inside of Set analysis

stabben23 — Wed, 13 Dec 2017 07:21:03 GMT

Will the use of maxstring instead of max change anything for you?

Re: Set Analysis inside of Set analysis

Anonymous — Wed, 13 Dec 2017 07:53:55 GMT

I had attached a sample .qvw, . I want to pick only one record with Max effective date. Any help is appreciated. I tried MaxString and turned to 0 value.

Re: Set Analysis inside of Set analysis

pradosh_thakur — Wed, 13 Dec 2017 09:02:24 GMT

Did you try the above code i sent . In set analysis if you have space while using >= or <= it will not work(it will not include that set and ignore it ) so remove if there is any space and try .

regards

Pradosh

Re: Set Analysis inside of Set analysis

tamilarasu — Wed, 13 Dec 2017 09:09:11 GMT

Hi Balaji,

Try this,

=sum({<Taxable={1}, TaxType={'03'}, Group={'0000'}, Item ={'000'},Effective={"<=$(=Max({<Taxable={1}, Group={'0000'}, Item ={ '000'}, TaxType={'03'}>} Effective))"} >}Distinct TaxRate)

Re: Set Analysis inside of Set analysis

sunny_talwar — Wed, 13 Dec 2017 09:32:22 GMT

Some of the values in your attached app are not available... I have changed your expression to what was available and it seems to give values

=Sum({<Taxable={1}, TaxType={'04'}, Group={'0100'}, Item ={'001'}, Effective={"$(=Max({<Taxable = {1}, Group = {'0100'}, Item = {'001'}, TaxType = {'04'}>} Effective))"}>} DISTINCT [Tax Tate])

Note, I tried it in a text box, but it should work in straight table also.

Re: Set Analysis inside of Set analysis

Anonymous — Wed, 13 Dec 2017 12:50:01 GMT

Thanks, Still have problem when I have more than one column in the table only one column (last One) has value all other column turned to 0, no set calculation happens. Not sure why this happening.

Any way thanks for the help in advance.

Re: Set Analysis inside of Set analysis

sunny_talwar — Wed, 13 Dec 2017 13:00:08 GMT

Would you be able to share an image of what you are seeing and explain what is wrong in the image?