Regarding Metrics as rows in QlikSense

[SK111]

Hello Friends,

Appreciate your time. We have a requirement having metrics in rows something like below image

The app is reduced by section access for each trader, There is a chart similar to it currently in excel we are now converting it using QlikSense, Appreciate your feedback!

Thank you!

[brunobertels]

for Example in straight table

create a calculated dimension with value list 

for example : 

=ValueList('metric1','metric2','metric3')

then add mesure like this

if(
ValueList('metric1','metric2','metric3') = 'metric1' ,
MesureMetric1,

if(
ValueList('metric1','metric2','metric3') = 'metric2' ,
MesureMetric2,

if(
ValueList('metric1','metric2','metric3') = 'metric3' ,
MesureMetric3,

)))

repeat the same for next colomn regular etc 

Hope it helps 

[SK111]

Thanks for your reply.  Below is how I tried.

dimesion: =valuelist('non_regular','regular','total')

expression:

If(Valuelist('non_regular','regular','total')='non_regular', sum([total sales non regular]),

If(Valuelist('non_regular','regular','total')='regular', (sum([total sales regular]),

If(Valuelist('non_regular','regular','total')='total', sum([total sales]))))

This renders the first row and I could get some thing like below using pivot table

I am looking for a way to get the rest of the metrics rows like sales %,cat sales and so on.,

the below ones

Thank you!

[Kushal_Chawda · Accepted solution]

@SK111  Create a table with below Dimension and measure

Dimension:
=ValueList('Trader -A Sales','Sales%','Cat Sales','Cat Sales%','Order Sales','Order Sales%')

Measure: (Total)
=pick(match(ValueList('Trader -A Sales','Sales%','Cat Sales','Cat Sales%','Order Sales','Order Sales%'),
'Trader -A Sales','Sales%','Cat Sales','Cat Sales%','Order Sales','Order Sales%'),
Trader -A Sales- Total Expression,
Sales%-Total Expression,
Cat Sales- Total Expression,
Cat Sales%- Total Expression,
Order Sales- Total Expression,
Order Sales%- Total Expression)

Measure: (Regular)
=pick(match(ValueList('Trader -A Sales','Sales%','Cat Sales','Cat Sales%','Order Sales','Order Sales%'),
'Trader -A Sales','Sales%','Cat Sales','Cat Sales%','Order Sales','Order Sales%'),
Trader -A Sales- Regular Expression,
Sales%-Regular Expression,
Cat Sales- Regular Expression,
Cat Sales%- Regular Expression,
Order Sales- Regular Expression,
Order Sales%- Regular Expression)

[SK111]

Great!, it worked:)

thanks a lot!

[SK111]

If I replace it with a Vizlib KPI Designer , will it be good with respect to performance vs the above solution.

The above one has pick and match , since all the metric calculations are in fly, will that be another good option to consider KPI.

Also, the metric Sales % should have a background color based on the metric values

the metric Order Sales should have an up or a down arrow based on the metrics values

So overall looks like KPI Designer is another good solution too.,

Could you kindly have your thoughts.

Thank you!

[Kushal_Chawda · Accepted solution]

@SK111  with pick match approach also you can do background colour and arrows. I have never used vizlib extensions in any of my project  due to restrictions . If it is easy to implement using extension you should try it out, but extension also have performance impact because it has overhead of loading into browser whenever it is referenced. I always prefer using native object unless it is not all possible to achieve any requirement.  You can note performance using both the options and then go with best approach.

[SK111]

sounds good, let me play more into it and choose the best .,appreciate it! Thank you!