topic Help Scripting in QlikView

Help Scripting

Tue, 13 Oct 2015 06:18:40 GMT

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

Re: Help Scripting

tresB — Tue, 13 Oct 2015 06:26:30 GMT

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) )

Re: Help Scripting

Tue, 13 Oct 2015 07:32:44 GMT

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

Re: Help Scripting

tresB — Tue, 13 Oct 2015 07:36:22 GMT

Could you explain your expected output against a selection in your sample provided?

Re: Help Scripting

Tue, 13 Oct 2015 07:59:00 GMT

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

Re: Help Scripting

tresB — Tue, 13 Oct 2015 08:21:46 GMT

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.

Re: Help Scripting

Tue, 13 Oct 2015 08:41:44 GMT

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.