Valuelist , pick match and if

[niranjana]

Hi , I have written and "If " expression inside Valuelist  function, but the first expression is working fine but second expression and third is not working. Also, they are working fine in separate text boxes

Dimension is : Valuelist('Total Market','ONC Market','NVS Onco')

Expression is :

if(Valuelist('Total Market','ONC Market','NVS Onco')='Total Market',

(((sum(aggr(((Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "}>}TL_Value))
/
Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "}>}Units_Value))*

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "}>}Units_Value),Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj])))

/

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "}>}PrevTL))-1,

if(Valuelist('Total Market','ONC Market','NVS Onco')='ONC Market',

(((sum(aggr(((Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'}>}TL_Value))
/
Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'}>}Units_Value))*

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'}>}Units_Value),Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj])))

/

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'}>}PrevTL))-1,

if(Valuelist('Total Market','ONC Market','NVS Onco')='NVS Onco',

(((sum(aggr(((Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}TL_Value))
/
Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}Units_Value))*

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}Units_Value),Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj])))

/

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}PrevTL))-1

)

Thanks in advance

[srchagas]

Hello

are you using a low of "(" [parentheses] , make sure you close  all they to make it work, i suggest you to use Visual studio or similar software tool to check  this cases, check if works now, because all the if was not have the parentheses closed.

if
  (
    Valuelist('Total Market','ONC Market','NVS Onco')='Total Market',
    (
     (
       (
        sum(
           aggr( 
                 (
                    (Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "}>}TL_Value))
                                                    /       
                     Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "}>}Units_Value)
                 )
                                                    *
                    Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "}>}Units_Value)
                ,Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj]
               )
            )

                                                    /
                    Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "}>}PrevTL)
        ) 
      )
    )
    
    -1,

if(Valuelist('Total Market','ONC Market','NVS Onco')='ONC Market',
  (
    (
      (sum(
           aggr(
                 (
                  (Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'}>}TL_Value))
            
                                                                         /
                    Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'}>}Units_Value)
                 )
                                                                         *
                    Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'}>}Units_Value)
                ,Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj]
                )
            )
       )
                                                                        /
                    Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'}>}PrevTL)
    )
    )-1,

if(Valuelist('Total Market','ONC Market','NVS Onco')='NVS Onco',
 (
  (
   (sum(
        aggr(
             (
              (Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}TL_Value))
                                                                        /
               Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}Units_Value)
             )
                                                                        *
               Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}Units_Value)
            ,Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj])
        )
    )
                                                                        /
              Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}PrevTL)
   )-1
)
)
)
)

 

[niranjana]

Thanks for your reply. it still doesnt work. Is aggregate the problem. For second and third exp I am getting -100% while for first its correct as 7.4%

[marcus_sommer]

Your problem is probably the combination of a valuelist() as dimension and the call of aggr() within the if-loops. Just for testing replace these aggr() with any simple sum() or count() to see that the construct will work principally.

That's not worked with the aggr() is caused from the fact that the valuelist() dimension is missing within the dimensions of the aggr(). But you couldn't place it there because only native existing dimensions will be accepted on this place. To bypass this restriction you could just transfer the logic into the script by creating the artificial dimensions there within an island-table maybe with a small inline-load like:

t: load * inline [
F
a
b
c
];

- Marcus

[niranjana]

Hi,

Thanks for you reply. I tried inline as well.  

Is there anything wrong with my expression. Should I remove aggregate ? but without that i am not getting correct value in text box.

Market_Inline:
Load * Inline [
Market_Inl
Total Market
ONC Market
NVS Onco
];

and my expression was 

=Pick(Match(Market_Inl,'Total Market','ONC Market','NVS Onco'),

(((sum(aggr(((Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'}>}TL_Value))
/
Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'}>}Units_Value))*

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'}>}Units_Value),Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj])))

+

sum(aggr(if( sum({<Year={"$(=Year(Max(Date))-2)"},Month={"<=$(=Num(Month(Max(Date))))"},flag={'Onco'}>}Units_Value)=0,

((Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'}>}TL_Value)
/
Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY'))"},flag={'Onco'}>}Units_Value))*

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY'))"},flag={'Onco'}>}Units_Value)),0),Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj])))

/

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'}>}PrevTL))-1,

(((sum(aggr(((Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "}>}TL_Value))
/
Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "}>}Units_Value))*

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "}>}Units_Value),Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj])))

+

sum(aggr(if( sum({<Year={"$(=Year(Max(Date))-2)"},Month={"<=$(=Num(Month(Max(Date))))"}>}Units_Value)=0,

((Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "}>}TL_Value)
/
Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY'))"}>}Units_Value))*

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY'))"}>}Units_Value)),0),Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj])))

/

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "}>}PrevTL))-1,

(((sum(aggr(((Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}TL_Value))
/
Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-2),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-2),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}Units_Value))*

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}Units_Value),Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj])))

+

sum(aggr(if( sum({<Year={"$(=Year(Max(Date))-2)"},Month={"<=$(=Num(Month(Max(Date))))"},flag={'Onco'},Distributor={'NOVARTIS'}>}Units_Value)=0,

((Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}TL_Value)
/
Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY'))"},flag={'Onco'},Distributor={'NOVARTIS'}>}Units_Value))*

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY'))"},flag={'Onco'},Distributor={'NOVARTIS'}>}Units_Value)),0),Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj])))

/

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY')) "},flag={'Onco'},Distributor={'NOVARTIS'}>}PrevTL))-1

)

[marcus_sommer]

I think your approach is too complex. Not from a technically point of view else from the handling during the development and a later documentation to enable a general maintaining.

Of course it's not impossible to use such expressions but to detect here any logically and/or syntax mistakes and/or data-related issues could become quite hard.

Therefore I suggest to simplify the approach. For example by removing the entire set analysis at first - maybe also the aggr() parts because you need an overview if all branches return a value. This ensured that there are no syntax issues. Then you could check if the results return the expected (in regard to no set analysis) values which cleared if all brackets within a branch are set to ensure the right order of execution. After that you will also see if you really need the aggr()  because this feature is often not mandatory necessary. And at last you add the set analysis again.

- Marcus 

[niranjana]

Hi Marcus,

Without aggregation, i am not getting the correct value. Also is it possible to us e NO distinct? If yes, where?

Thanks

niranjana

[marcus_sommer]

The final results are irrelevant - at least at the beginning. Here it's important that the syntax and the branching and the main-logic within them is working and then step by step more logic and conditions could be added and checked and so on. 

Within your expression are hundreds of possibilities which might cause a wrong result or an error, like missing or too much brackets and quotes, a wrong execution order with your operators, missing/wrong dimensions within the aggr(), wrong formatting and many more reasons. Therefore my suggestion from above to solve the issue with a systematically approach - just with trial and error caused usually much more pain ...

An expression should provide at the first glance the information what's going on. If you need to scroll between dozens of lines and to disassemble multiple parts again and again - it's too complex. Maybe the set analysis and the expression-parts could be simplified with (parametrized) variables ...

Beside this NODISTINCT could be in general applied but usually it make the things not easier else the opposite happens.

- Marcus 

[niranjana · Accepted solution]

Hi Marcus,

Thanks a lot for your explanation!! I know the expression is long. But I found a roundabout way. I added an inline table for markets as shown above. Also in the expression , I added the inline dimension in aggregate function apart from other dimensions. And I got the result.

i.e. 

Sum({<Date={">=$(=Date(Addyears(YEarstart(Max(Date)),-1),'MMM/YY')) <=$(=Date(Addyears(YEarend(Max(Date)),-1),'MMM/YY'))"},flag={'Onco'},Distributor={'NOVARTIS'}>}Units_Value)),0),Product,Pack,Distributor,ATC4,[Ret/Hosp],[Oral/Inj],Market_Inl)))