Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

richardkroutil
New Contributor II

Dynamic Expressions per Row Using If() no Set Analysis

So this might not even be possible or so obvious that I have blinders on at this point. That being said I would like to load a list of measures from and Excel document, including some meta data about the dimension and an expression. I started with Set Analysis but after reading, realized that the calculation performs once for the chart and not on a row level, So I have implemented If() statements which I thought might be different but the results are similar to what Set Analysis was doing.

I have scaled back a sample application of what it is I was trying to do for reference and got the same behavior.

In the load script I have:

//Sample Sales

Sales:

Load * Inline

[

  Counter,Store,Year,Month,Sales

  1,A,2014,Jan,1

  1,A,2014,Feb,1.5

  1,A,2014,Jan,2

  1,A,2014,Feb,3.5

  1,A,2014,Jan,4

  1,A,2014,Feb,4.5

  1,A,2014,Jan,5

  1,A,2014,Feb,5.5

  1,B,2014,Jan,1

  1,B,2014,Feb,1.5

  1,B,2014,Jan,2

  1,B,2014,Feb,3.5

  1,B,2014,Jan,4

  1,B,2014,Feb,4.5

  1,B,2014,Jan,5

  1,B,2014,Feb,5.5

];

//Sample Expression Data Island

_Expressions:

Load * Inline

[

  Name,myExpression

  Sales Total (Amount),"Sum(If(Month='MM' and Year='YYYY', Sales,0))"

  Sales Total (Transactions),"Sum(If(Month='MM' and Year='YYYY', Counter,0))"

]

In the chart, I have a table with the Name of the Expression as the dimension and a couple of sample expression fields for months, Jan and Feb which are manually wired in at this time.

SampleDesign.JPG.jpg

As you can see I get - values, however if I select a single record from the measure Name list, the calculations perform as expected.

SampleDesign1.JPG.jpg

If anyone has any comments or suggestions, I would greatly appreciate it. Keep in mind my example is sample only, the actual project was slated to have many more measures.

I have attached the sample qvw file as well.

Thanks in advance.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Dynamic Expressions per Row Using If() no Set Analysis

I think what you're looking for is the solution Jonathan Dienst posted here: Re: Expressions in Fields


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
MVP & Luminary
MVP & Luminary

Re: Dynamic Expressions per Row Using If() no Set Analysis

I think what you're looking for is the solution Jonathan Dienst posted here: Re: Expressions in Fields


talk is cheap, supply exceeds demand

View solution in original post

jsaradhi
Valued Contributor

Re: Dynamic Expressions per Row Using If() no Set Analysis

added two charts (green color) and made some changes that should make it lot simpler...

richardkroutil
New Contributor II

Re: Dynamic Expressions per Row Using If() no Set Analysis

Thank You Gysbert ... that first sentence summed it up for me.

MVP & Luminary
MVP & Luminary

Re: Dynamic Expressions per Row Using If() no Set Analysis

I've attached an example to my original post that shows another trick, i.e. passing parameters to the expression in the variable. That way you don't need to add expressions for each month. You can pass the values of the Month field to the expression. See the pivot table below the Year listbox for an example.


talk is cheap, supply exceeds demand
Highlighted
richardkroutil
New Contributor II

Re: Dynamic Expressions per Row Using If() no Set Analysis

I can't thank you enough ... very elegant.