5 Replies Latest reply: Mar 28, 2014 5:33 PM by Richard Kroutil RSS

    Dynamic Expressions per Row Using If() no Set Analysis

    Richard Kroutil

      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.