How to treat "$Field values" as "Dimensions"

[Manish_Kumar_]

Let's say I have an app with 3 fields of which are 2 are KPIs and 1 is Dimension

Now I want to make below chart in the app

$Field    sum($Field)

Sales     10,000

Product  -    

Quantity 40

Note: sum(Product) will be null as Product is in alphanumeric

By making this chart, I want to validate KPIs as I am migrating QlikView to Qlik Sense and the main app has more than 100 KPIs .

MAIN PROBLEM HERE IS - sum($Field) is not working. So If anybody has any idea to make it work then please suggest.

[vikasmahajan]

Hi,

you can use  nested parameterized variables in QS to achieve this :

https://www.youtube.com/watch?v=7QWxoBDilko

Vikas

[Manish_Kumar_]

Hi @vikasmahajan ,

Appreciate your quick reply.

I have gone through the video you provided, but I can not find out how to use that to solve my use case.
If you could provide me some explanation or hint towards my use case, that would be really helpful!

Manish

[marcus_sommer · Accepted solution]

You could query against $Field with a $-sign expansion but only one at a time, for example with:

count([$(=$Field)])

which requires that one value of $Field is selected. Returning multiple ones within a chart is only possible if appropriate branches are used like:

if(Dim = 'x', Expr1, if(Dim = 'y', Expr2, ....

which is probably not very expedient.

[Manish_Kumar_]

One at a time works perfect @marcus_sommer 

But I need a way out to evaluate the multiple ones, as in my actual app I have more than 100 KPIs and will take forever to validate them manually one by one. 

This could be a really helpful use case generally for all Qlik Developers.
Will appreciate if others can discuss this here.

[marcus_sommer]

Like mentioned it's not directly possible. The described workaround could of course be done a bit more elegant, like:

pick(rowno(),
sum([$(=subfield(concat($Field, ','), ',', 1))]),
sum([$(=subfield(concat($Field, ','), ',', 2))]),
sum([$(=subfield(concat($Field, ','), ',', 3))]))

by $Field as native dimension - but you need to add so much branches like you want to have in the chart and the bold numbers are needed to set manually - because they must be set within the $-sign expansion which excludes the use of rowno(). More complicate becomes such an approach if also a certain order of $Field should be shown because the dimension-order and the concat-order would be needed to synchronize.

Beside this I doubt that such approach is really helpful for developers. For me it hints more for a not really suitable data-model if there are more than 100 of measure-fields probably coming from a crosstable-structure. In my experience this is simple to load but very hard to handle within the UI. IMO better is mostly a transform within a data-stream-structure with a KPI and a Value field and then the KPI could be the dimension and sum/count(Value) would return the results - respectively within other views the relevant KPI could be defined within a set analysis.

[Manish_Kumar_]

Thanks @marcus_sommer  for your idea.

I Appreciate this way out:

pick(rowno(),
sum([$(=subfield(concat($Field, ','), ',', 1))]),
sum([$(=subfield(concat($Field, ','), ',', 2))]),
sum([$(=subfield(concat($Field, ','), ',', 3))]))

This would work, despite that the speed would be questionable.

I didn't get this point of yours "IMO better is mostly a transform within a data-stream-structure with a KPI and a Value field and then the KPI could be the dimension and sum/count(Value) would return the results - respectively within other views the relevant KPI could be defined within a set analysis.".

If you are suggesting performing this in backend, then How could I possibly do that because idk if $Field could be used in backend. And My app has around 50 tables following Snowflake schema, so calculating each and every KPI will be again a manual process. Are you suggesting some general work around to this by your above statement? If Yes then Please help me out.

I really appreciate your suggestions though!! 

Thanks Again!

[marcus_sommer]

A real snowflake scheme is only an extended version of a star-scheme just with a bit more normalization by the dimensions. This means only a single fact-table exists with all relevant measure-fields. If there are more than 100 measure-fields they are structured as crosstable. It's a structure which is quite often used in Excel, for example with horizontal period-fields (each period in a single column) and the values are below in the rows. Not so obvious but also a crosstable might be sales-data which haven't a single value else various cost-parts and taxes, discounts and so on - also divided in single columns each - and the same might exists for planning and forecasting data.

Keeping all these information within separate fields is quite tedious - already in the script and especially within the UI. Each one must be directly referenced within each view. Especially by complex calculations which may include dozens fields it's a nightmare ...

Therefore my suggestion to consider appropriate changes within the data-model. It was not meant to aggregate everything within the script else just resolving crosstable-structures: The Crosstable Load - Qlik Community - 1468083

[Manish_Kumar_]

Thanks @marcus_sommer . I got my clarification.
And Thanks @vikasmahajan for replying