Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hkkwon89
Contributor
Contributor

Different expression each row in a table

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

DimensionBed days/KAdmits/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

1 Solution

Accepted Solutions
prieper
Master II
Master II

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

View solution in original post

6 Replies
juraj_misina
Luminary Alumni
Luminary Alumni

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

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
Contributor
Contributor
Author

Hi Peter,

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

juraj_misina
Luminary Alumni
Luminary Alumni

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
Contributor
Contributor
Author

Thank you so much for your help!

Anonymous
Not applicable

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