topic Re: Valuelist , pick match and if in App Development

Valuelist , pick match and if

niranjana — Thu, 10 Mar 2022 08:04:26 GMT

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

Re: Valuelist , pick match and if

srchagas — Thu, 10 Mar 2022 09:33:55 GMT

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

Re: Valuelist , pick match and if

niranjana — Thu, 10 Mar 2022 10:11:13 GMT

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%

Re: Valuelist , pick match and if

marcus_sommer — Thu, 10 Mar 2022 10:15:46 GMT

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

Re: Valuelist , pick match and if

niranjana — Thu, 10 Mar 2022 11:07:49 GMT

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(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({<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(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

)

Re: Valuelist , pick match and if

marcus_sommer — Thu, 10 Mar 2022 12:08:57 GMT

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

Re: Valuelist , pick match and if

niranjana — Thu, 10 Mar 2022 13:20:00 GMT

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

Re: Valuelist , pick match and if

marcus_sommer — Thu, 10 Mar 2022 13:54:08 GMT

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

Re: Valuelist , pick match and if

niranjana — Fri, 11 Mar 2022 12:18:17 GMT

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