Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
MVP

Try this

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

39 Replies
MVP

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
Not applicable
Author

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

Master II

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

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
Not applicable
Author

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
Not applicable
Author

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

Anonymous
Not applicable
Author

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)

Partner - Champion III

>>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
Anonymous
Not applicable
Author

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

Community Browser