Different expression each row in a table

[hkkwon89]

Hello

I have a problem I can't seem to figure out..

I need to create a table with different expressions in each row and custom dimensions.

Product
Senior
Commercial
Medi-Cal

Product 2
SNPD
CAL MEDICONNECT
POS
EXCHANGE
SNPC
LIC
SALUD
OTHER
TANF
ALLIANCE
LTC
MCE

Region
DO
SF
SL
WH

I am calculating number of admits(0-2) per 1,000 and bed days(1-31) per 1,000.. 

for example, below is admit/1,000 expression

=SUM({<AdIPType={'ACUTE'}>} ADMIT)/COUNT(DISTINCT DateofService)/[AVG MBRSHP]*12000

but i need a very specific table as below

Dimension	Bed days/K	Admits/K
Product= Senior, Product 2 = SNPD, SNPC  (Actual label for the colum will be Senior with SNPD,SNPC)
Product = Medi-Cal
SF Region Medi-Cal
Product = Commercial, Product2 = EXCHANGE, Region= DO

How would i go about doing this? So basically an end user will only pick time frame filter (YEAR, Quarter, etc). I need those dimensions fixed and bed days and admits to calculate according to the specific dimension in each row. Also need this in a table so it can be exported into excel.

Any help is appreciated.

Thanks

[juraj_misina]

Hi Caleb,

I usually try to prepare my data in the data model as much as possible to fit my needs, but this seems to be a very specific requirement. I such cases I do a nasty thing and use ValueList() function. You can create a calculated dimension (replace Dim1 etc with your dimension values 'Product = Senior...'):

ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')

And then an expression:

Pick(Match(ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4'), 'Dim1', 'Dim2', 'Dim3', 'Dim4'),

Expression for Dim1,

Expression for Dim2,

Expression for Dim3,

Expression for Dim4)

Be careful though, my exprerience is that these kind of requirements tend to be superspecific and rather complex, so it might not perform well on large datasets.

Hope this helps

Juraj

[prieper · Accepted solution]

a bit more performant is to create a dataisland with DUAL:
Dimensions:

     LOAD DUAL(Dim, Sort)     AS Dim INLINE [Dim, Sort

                                                                 Dimension1, 1

                                                                 Dimension2, 2

                                                                 Dimension3, 3];

Choose then "Dim" as Dimension

Expression then might be

PICK(Dim,

//     Calculation 1

SUM({<Product = {"Senior"}>} Amount),

//      Calculation 2

SUM({<Product = {"Medi"}>} Amount),

//     Calculation 3

SUM({<Product = {"Senior", "Medi"}>} Amount))

Have not noticed slow performance,

however can underline Juraj's comment, that such report is super-inflexible.

Another advantage is that within the Island-table you may also fix some formatting like Bold or Background-colours.

Peter

[hkkwon89]

Hi Peter,

Does pick function then choose the expression in the sort order?

[juraj_misina]

Basically yes. The first argument of pick function is a positive integer based on which Pick chooses wich expression to return/evaluate. In Peter's example Dim field has both string and numeric value, so you can use it as an input for Pick() to return corresponding calculation.

[hkkwon89]

Thank you so much for your help!

[Anonymous]

Hello,

just jumping in on this, I tried your solution Juraj_misina but there seems to be a subtelty I am missing... 

I tried something like this, but the result is wrong for Dim3. Any insight on this?

Pick(Match(ValueList('Dim1', 'Dim2', 'Dim3'), 'Dim1', 'Dim2', 'Dim3'),

sum(a),  //Expression for Dim1

sum(b), //Expression for Dim2

sum(a)/sum(b)) //Expression for Dim3