Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have an existing Qlik file, in which I have a chart with the following script (which gather data from different locations):
Aggr (Sum (Money),[Day/Month/Year],MoneyType)
This expression works fine in a line chart (in Which I have different selection lists like MoneyType, Country, City, Date Year, Month, Day
I am\was looking for an expression that present in a text box , when I select a random period, how much Income do I lose from the start date to the end date.
Now I managed to write an expression in a test environment for a SIMPLE sum expression and It works fine too:
Sum({<Date={'$(=MIn(Date))'}>} Qty1)
Attached is the test environment which is not 1 to 1 like the production (in which the period selection option is much wider , flexier)
Now to my question-problem , since I am not SO! strong yet in the scripting world.
I need to write an expression which show, in a text box, the min (earliest) or max (latest) value for my Aggr expression.
I can not attach the real data files.
Your help will be appreciated.
Nir
If I only take
Nir Gil wrote:
.....
Aggr (Sum (Money),[Day/Month/Year],MoneyType)
....I need to write an expression which show, in a text box, the min (earliest) or max (latest) value for my Aggr expression.
since I did not get it properly (perhaps), you could probably try like:
Max(Aggr (Sum (Money),[Day/Month/Year],MoneyType) )
Hi,
Well from some reason when I use the
the result is that I get the MAX value from selected period instead of the Latest vale , which is what I am looking for and which work fine in my test environment (for the expression: Sum({<Date={'$(=Max(Date))'}>} Qty1)
I assume the Max should be integrated before the Day\Month\Year field :
Like: Aggr (Sum (Money),MAX[Day\Month\Year,Moneytype) - but I can not find the correct script for that.
Nir
Could you explain your expected output against a selection in your sample provided?
OK,
In my provided sample the results are as expected!
I want to show the Latest (Max) {and the earliest(Min)} value from the selected period which the user select in external year, month and day list boxs. (For example the user can select the last week, April-May 2014, 13-18.6.2015 and set)
In my real world, I use a more complicated aggregated-Sum expression.
But when I use: Max(Aggr (Sum (Money),[Day/Month/Year],MoneyType) ) the out put is not the latest value (from the selected period) , it is the largest (max) value.
The max should be somehow scripted in a condition , I guess, like in the sample result.
Nir
May be like:
FirstSortedValue( Aggr (Sum (Money),[Day/Month/Year],MoneyType), - [Day/Month/Year])
This would give you recent most amount provided you time dimension [Day/Month/year] is a real timestamp/date field.
I appreciate your effort,
The suggested expression does not work. (no result is shown)
Whereas : Max (Aggr (Sum (Money),[Day/Month/Year],MoneyType)) - works BUT bring the MAX value , not the latest.
And Yes - Day/Month/Year is a real date field.