Accepted Solutions
Hi,
Firstly I encourage you to install QS Feb 2019 release as soon as it has amazing feature to "expand formulas", e.g. to show what exactly is passed to the engine as per picture below:
Another problem you had was separating search string... for some reason if you write
{"
<$(=...)works differently to
{"<$(=...)...One more thing: never apply function on field in set analysis like you did in example:
...
{< Date(TRANSACT_DATE)={....}>}Above function Date() will never work.
Next, values returned inside set analysis should have exactly the same format as your field, e.g. if your field is loaded as number 43523, then in set analysis you must have the same numeric format, if it's a date, then it should be a date and so on.
The last, but not the least, from performance point of view NEVER keep timestamps in your Qlik data model, because you won't get compression on that field as soon as every value will be unique (as every moment in our life 🙂 ) and for the same day, say March, 9th, you can get 86400 unique timestamps on "seconds" level (or 86400000 if you have ms included). So better to split Date and Time separately, if you don't need to report on time - get rid of it, if you need hours, keep only 24 hours, etc..
PFA example with working formula.
Hope this helps
und Vielen Glück!
//Andrei
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:
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
Thx for your help, but it still doesn't work.
Probably i am doing something wrong..
I made a small demo, maybe you can try to see what a do wrong.
Many thx in advance!
PS,
fyi; in my original app, the field 'TRANSACT_DATE', is a datetime field.
I dont need to format it to present it is a date or time. Please see below.
This single set analysis working just fine, but the dynamic sum set analysis however.... 😞
(Max({ <VerbruikMeetbaar={'J'}, PRODUCT_CODE={'1'}> } TRANSACT_DATE))
Hi,
Firstly I encourage you to install QS Feb 2019 release as soon as it has amazing feature to "expand formulas", e.g. to show what exactly is passed to the engine as per picture below:
Another problem you had was separating search string... for some reason if you write
{"
<$(=...)works differently to
{"<$(=...)...One more thing: never apply function on field in set analysis like you did in example:
...
{< Date(TRANSACT_DATE)={....}>}Above function Date() will never work.
Next, values returned inside set analysis should have exactly the same format as your field, e.g. if your field is loaded as number 43523, then in set analysis you must have the same numeric format, if it's a date, then it should be a date and so on.
The last, but not the least, from performance point of view NEVER keep timestamps in your Qlik data model, because you won't get compression on that field as soon as every value will be unique (as every moment in our life 🙂 ) and for the same day, say March, 9th, you can get 86400 unique timestamps on "seconds" level (or 86400000 if you have ms included). So better to split Date and Time separately, if you don't need to report on time - get rid of it, if you need hours, keep only 24 hours, etc..
PFA example with working formula.
Hope this helps
und Vielen Glück!
//Andrei
I cheered to soon..
((MAX({ <VerbruikMeetbaar={'J'}, PRODUCT_CODE={'1'}> } VerbruikKM))-(MIN({ <VerbruikMeetbaar={'J'}, PRODUCT_CODE={'1'}> } VerbruikKM)))/(Sum({
$<TRANSACT_DATE={">$(=min({$<VerbruikMeetbaar = {'J'}, PRODUCT_CODE={'1'}>}TRANSACT_DATE)) <=$(=max({$<VerbruikMeetbaar = {'J'}, PRODUCT_CODE={'1'}>}TRANSACT_DATE))"}> }VerbruikAantalLiter))
The formula above is working perfectly when I have selected only 1 vehicle,
but without a vehicle selection QS generates a sum with all the galons from a vehicle from the minimum date (correct) , without checking the maximum date.
One vehicle selection:
No Vehicle selection :
Maybe you know how I can solve this?
How are you limiting your min/max dates?
Generally speaking expressions in set analysis inside $(= ... ) are working on a global set, which means this min/max numbers are calculated disregarding any dimensions in your chart, when you selecting 1 car - basically there is no other possible values (except correct:) ), however when you don't have selections applied it will calculate max() on entire data set limited by other selections.
So, taking your previous example with inline table, expression evaluates in below:
which is correct on global data set
as you see, vehicles are different, so further your MeasureAcceptable nor PRODUCT_CODE are not considered in calculations at all... then you can add this separately:
=Sum({
<TRANSACT_DATE={
">=$(=min({$<MeasureAcceptable = {'J'}, PRODUCT_CODE={'1'}>}TRANSACT_DATE))<=$(=max({$<MeasureAcceptable = {'J'}, PRODUCT_CODE={'1'}>}TRANSACT_DATE))"
}, MeasureAcceptable = {'J'}, PRODUCT_CODE={1}> }Gallons)However, in this case it's not quite clear to me why do you need these dates in set analysis?
Hope this helps.
UPD: Forgot attachment. (PFA)
//Andrei
Wow, that was new for me.. Thx! 🙂
It costed me some time to understand, but I am sure these principles will be useful some time.
Unfortunately I doubt if I can use it for this purpose.
Just a example from the demo app. As I said I wanted to calculate the performance in different (dynamic) time selections.
I THINK that this method of flags and borders are to static for this.
See small example below. This first row has a '1' as flag, but for a right calculation the flag should be a 0.
