Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Help Scripting

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

6 Replies
MVP
MVP

Re: Help Scripting

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

Not applicable

Re: Help Scripting

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

MVP
MVP

Re: Help Scripting

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

Not applicable

Re: Help Scripting

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

MVP
MVP

Re: Help Scripting

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.

Not applicable

Re: Help Scripting

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.

Community Browser