topic Re: SET EXP in QlikView

SET EXP

lavielme — Thu, 02 Jun 2016 12:46:48 GMT

Hey everyone

Lats say i have this data:

Company	Date	Stoc
X	30/05/2016	10
Y	31/05/2016	20
Z	31/05/2016	30
X	30/04/2016	40
Y	01/05/2016	10
Z	02/05/2016	30
X	28/03/2016	40
Y	31/03/2016	50
Z	31/03/2016	10
X	27/03/2016	20
Y	31/03/2016	30
Z	01/04/2016	40

and i need an expression who can Calculate For each Company the last stock in a table for example if i don't choose anything

the table will be like that:

Company Stoc

X 10

Y 20

Z 30

If i choos march and aprill

Company Stoc

X 40

Z 40

Y 0

Best Regards

Lavi

Re: SET EXP

swuehl — Thu, 02 Jun 2016 14:01:00 GMT

Try Company as dimension in your chart and

=FirstSortedValue(Stoc, -Date)

as expression. Take care that your Date field values need to show a numeric representation.

Re: SET EXP

lavielme — Thu, 02 Jun 2016 14:04:42 GMT

Can I Do FirstSortedValue(Stoc, -Date(Date,'DD/MM/YYYY'))

Re: SET EXP

sunny_talwar — Thu, 02 Jun 2016 14:16:51 GMT

May be this:

=FirstSortedValue({<MonthNum = {"$(=Max(MonthNum))"}>}Aggr(Sum(Stoc), Date, Company), -Aggr(Date, Date, Company))

Script:

Table:

LOAD *,

Month(Date) as Month,

Num(Month(Date)) as MonthNum;

LOAD * INLINE [

Company, Date, Stoc

X, 30/05/2016, 10

Y, 31/05/2016, 20

Z, 31/05/2016, 30

X, 30/04/2016, 40

Y, 01/05/2016, 10

Z, 02/05/2016, 30

X, 28/03/2016, 40

Y, 31/03/2016, 50

Z, 31/03/2016, 10

X, 27/03/2016, 20

Y, 31/03/2016, 30

Z, 01/04/2016, 40

];

Without Selection

With Selection

Re: SET EXP

swuehl — Thu, 02 Jun 2016 15:14:51 GMT

If you need to interpete your date values, this should work

FirstSortedValue(Stoc, -Date#(Date,'DD/MM/YYYY'))

But it would be better to do in the script.

SET DateFormat = 'DD/MM/YYYY';

Re: SET EXP

sunny_talwar — Thu, 02 Jun 2016 15:14:56 GMT

Re: SET EXP

lavielme — Thu, 02 Jun 2016 15:20:37 GMT

why the total line is null()???

Re: SET EXP

swuehl — Thu, 02 Jun 2016 15:34:32 GMT

You can also try something like

=Sum( Aggr( If( Date = Max(Total<Company> Date), Sum(Stoc), Company, Date))

Again, take care that Date shows a numeric representation.

Re: SET EXP

lavielme — Thu, 02 Jun 2016 16:52:40 GMT

Thank you very much, Sunny. That was the exactly thing that I searched.

Note: Edited by Community Moderator. Please do not post in all capital letters.

Re: SET EXP

Thu, 02 Jun 2016 17:21:02 GMT

Hi,

Total null can be set by changing total mode in expression tab from. select "sum of rows".

Thanks

Re: SET EXP

sunny_talwar — Thu, 02 Jun 2016 17:23:12 GMT

What would you like to see for the total line?

Re: SET EXP

lavielme — Thu, 02 Jun 2016 17:58:48 GMT

‌i solved that with aggr thank's

Re: SET EXP

swuehl — Thu, 02 Jun 2016 18:04:53 GMT

If you want to filter the month, you can also add the set expression to my last expression:

Sum(

{<MonthNum = {"$(=Max(MonthNum))"}>}

Aggr( If( Date = Max(Total<Company> Date), Sum(Stoc)), Company, Date))

This should return the correct total, too. And saves you one advanced aggregation

Company	=FirstSortedValue({<MonthNum = {"4"}>}Aggr(Sum(Stoc), Date, Company), -Aggr(Date, Date, Company))	Sum({<MonthNum = {"4"}>} Aggr( If( Date = Max(Total<Company> Date), Sum(Stoc)), Company, Date))
	40	80
X	40	40
Z	40	40

Re: SET EXP

sunny_talwar — Thu, 02 Jun 2016 18:06:39 GMT

But Stefan we are already in the straight table, why even need to use Aggr() for that. Why not use the total mode as pointed by Amit‌?

Re: SET EXP

swuehl — Thu, 02 Jun 2016 18:09:41 GMT

Which of your aggr() function would you drop then?

Re: SET EXP

sunny_talwar — Thu, 02 Jun 2016 18:10:54 GMT

I did not mention dropping any aggr . My comment was related to getting the total right

Re: SET EXP

swuehl — Thu, 02 Jun 2016 18:13:31 GMT

Sure, you can change the total mode.

I personally prefer a solution that will also work in a text box or when changing the chart to a pivot table, and if this solution comes with no additional cost, fine.

Re: SET EXP

sunny_talwar — Thu, 02 Jun 2016 18:16:40 GMT

I always thought that Aggr() function should be avoided as it can be resource extensive. Although I am using two in my expression, but in general it seems that you are saying that

Avg(Aggr(Sum(Sales), Product)) is better than using Sum(Sales) and using Avg as total mode (in straight table)?

Re: SET EXP

swuehl — Thu, 02 Jun 2016 21:48:36 GMT

Hi Sunny,

sorry, it's getting late in here.

Not sure if I understand what you are trying to tell me.

I would agree that in general, I try to avoid the aggr() function since it's creating some overhead and may also show issues w.r.t the (chart) dimensions it's getting projected on.

It all depends on the requirements and context, but if I can chose between two expressions that return the same on the details lines with a similar complexity, but one delivers the correct totals (correct w.r.t to my requirements) and can also be kept when changing e.g. the chart style, I would prefer this over the other.

This does not mean that the other expression is wrong.

Re: SET EXP

sunny_talwar — Fri, 03 Jun 2016 01:53:08 GMT

Got it. Thanks for taking out time to go over this in detail for us