Set Analysis inside of Set analysis

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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

[pradosh_thakur]

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

[devarasu07]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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)

[jonathandienst]

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

[Anonymous]

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