QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save \$400. Learn More
Highlighted
New Contributor II

Set Analysis inside of Set analysis

Hi

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.

Tags (1)
1 Solution

Accepted Solutions MVP

Re: Set Analysis inside of Set analysis

Try this

=FirstSortedValue(DISTINCT {<Taxable={1}, TaxType={'04'}, Group={'0100'}, Item ={'001'}>} [Tax Tate], -Effective)

39 Replies MVP

Re: Set Analysis inside of Set analysis

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

New Contributor II

Re: Set Analysis inside of Set analysis

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

Honored Contributor II

Re: Set Analysis inside of Set analysis

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

Learning never stops.
Honored Contributor II

Re: Set Analysis inside of Set analysis

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)

or

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

New Contributor II

Re: Set Analysis inside of Set analysis

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.

New Contributor II

Re: Set Analysis inside of Set analysis

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

Contributor

Re: Set Analysis inside of Set analysis

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) MVP

Re: Set Analysis inside of Set analysis

>>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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
New Contributor II

Re: Set Analysis inside of Set analysis

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