
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- « Previous Replies
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this
=FirstSortedValue(DISTINCT {<Taxable={1}, TaxType={'04'}, Group={'0100'}, Item ={'001'}>} [Tax Tate], -Effective)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I tried and not working. Any other mistake that you can see in it ?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I tried to use single quote and the results are same , Chart pick up all records on calculation regardless max effective date.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
>>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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks , if I remove it then results turns to zero.

- « Previous Replies
- Next Replies »