Set Analysis with multiple variables

[aronvermeulen]

Hi all,

I am trying to make a set analysis  with multi set analysis's, including variables.

I have tried several options/methods, the latest is maybe the easiest to read, see below.

Note: In my table not every Row is acceptable due to wrong milliage.

Thats why I have build a qualifier (MeasureAcceptable). Can somebody tell me how I can solve this?

=Sum({
$<
TRANSACT_DATE={
">$(=min({$<MeasureAcceptable = {'J'}, $PRODUCT_CODE={'1'}>}TRANSACT_DATE))"

"<=$(=max({$<MeasureAcceptable = {'J'}, $PRODUCT_CODE={'1'}>}TRANSACT_DATE))"

}
> }TotalGallons)

[crusader_]

Hi,

I believe I found a solution, however it's not so graceful as I want it to be, but still it solves the problem.

As you already know Set Expression is calculated once per chart, but not per individual record, so in your case we need to put different expressions for each dimension value, e.g. vehicle, so we end up with expression like below:

PICK ( MATCH( Dim1, 'val1','val2',...), exp1, exp2,...)

Obviously no one wants to write this expression manually, so we need to generate it.

Please find attached example, check script/variable comments carefully. In case of any questions feel free to ask (solutions are a bit tricky).

//Andrei

[aronvermeulen]

I am really impressed in your skills! For me this is really next level.. 🙂

But besides that i don't fully understand the method, i cannot calculate to the outcome of this solution.
Can you explain the outcome, or is there something wrong in the script?

In my opinion it should have been 1856 gallons . Or maybe 2293 If you add the transaction of 16-01-2019.

[crusader_]

Hi,

I don't know from which point we have different numbers, but if you check QS table carefully you'll notice that gallons in your Excel do not match to numbers in QS. As soon as I don't have any other sources, I exported straight table from the bottom to excel and did the same calculation in Excel:

All values for 13133 vehicleSelected values for 13133 vehicle

Just in case please find attached my the latest QS file (I've truncated decimal part of the date, just to have single value instead of Date per vehicle, because even if you apply DATE() conversion on front end - it doesn't change underlying data, so internally values are still different even if it looks the same in list box).

Regarding solution, if you ask more specific question, I'll try to explain.... Perhaps I need to start my blog 🙂

//Andrei 

[aronvermeulen]

You're right. I have no idea where the data difference is coming from. However sorry!

But wow, what a solution!! I wish I was able to ask some specific questions, but this is 2 levels above my actual competence level. I started by studying each formula and function. But it is still a bit abacadabra.. 🙂

I am almost embarrassed to ask, but for my performance measure it is important that i calculate the gallons from* the first measurable transaction. (* The  gallons of the first transaction should not be in the total)

In the example below the total should be 1414 gallons/liter. (473+503+438)

If I have the calulation above I can calculate the performance myself with the other component;
- Millage 179181-174875= 4306 kilometers
- The performance of this vehicle in this selection = 3.05 (#km/1L)

[crusader_ · Accepted solution]

Never hesitate to ask, how otherwise you could learn 🙂 ?

First of all, have a look into 3 expression like on picture below. #1 is a manual one which explains what we want to achieve, #2 and #3 are exactly the same thing, just made with different approaches. (All of them are exactly the same)

From Manual (#1) expression you would notice that SUM(...) part is exactly the same except vehicleID, as soon as we don't want to write it manually and moreover maintain it manually, we're creating a parameterised variable vExpression  (<you can call it whatever name, but don't forget to refer to it properly>) where defining business rule

You can pass a parameter into variable instead of value like on below screenshot

Once you defined variable you would want to call it in your chart like $(vExpresssion(13133)) :

As you see we're using vExpression - text as a prototype for future variable usage (# sign is used to prevent immediate calculation - because we need to build full expression first). Concat() function - just concatenates values into single string, so our interim goal to get a text line as below:

This expression evaluated into below string

Our interim goal

And the last step is just replace #-sign with $-sign - allowing the text become a real variable and calculate results.

I've added manual expressions for mileage in attached example, so you can try to generate it in the same way as it's done for Gallons.

NOTE: I'm using [TRN DATE] field because in set analysis number is rounded to nine digits after comma, however in load script it has 10, so TRANSACTION_DATE in numeric format will never match exactly, because 43495.516666667 is not equal to 43495.516666667, however it works in <= / >= expressions just because smaller/bigger indeed.

If the data from vehicles is not read twice (several times) a day, remove time from your data model (If you need to report on it - keep it separate)

Hope this helps

//Andrei

[aronvermeulen]

This helps a lot!! Thanks very much!  I will (try ) to build it in my live app 

Unfortunately  I can give only 1 like, but you deserve much more!! 🙂

[crusader_]

Thanks for kind words! Hope you'll manage this to work.

Good luck!

//Andrei