topic Re: Use expression without limiting selection in QlikView

Use expression without limiting selection

Sat, 14 Dec 2013 22:57:16 GMT

Hi all,

Please see code;

Aggr(sum({<Object = {'ZZ0012'}>}Volume),MonthYear)/

Aggr(sum({<Object = {'ZZ0012'},MonthYear={'Mar-2012'}>}Volume),MonthYear)

The second line is meant to be a calculated value, but without restricting selections on months which it does right now. So how can I use MonthYear March 2012 without limiting the selection?

Thanks and regards,

Tom

Re: Use expression without limiting selection

Sat, 14 Dec 2013 23:15:33 GMT

Let me understand this correctly - you want the second line NOT to be limited to Mar-2012?

But you want it to start with march-2012 or?

If you remove MonthYear={'Mar-2012'} there is no filter on monthYear

Otherwise you could try using getfieldselection and then trigger the document to start up in march

Re: Use expression without limiting selection

qlikpahadi07 — Sun, 15 Dec 2013 05:01:37 GMT

Hi Tom,

If I am not wrong then you can split Month year as

Aggr(sum({<Object = {'ZZ0012'},Year = {$(=Max(Year)), Month= {$(=Max(Month)) >}Volume),MonthYear)

Re: Use expression without limiting selection

Sun, 15 Dec 2013 07:43:00 GMT

Thomas/Pahad,

Thanks for so quick a response. Unfortunately, It is not what I am after, but I probably should have explained more.

I specifically want the sum of the volume, aggregated per March 2012 as a value, because that is the chosen baseline month. That means I do no want a minimum month.

Say that March has as summed/aggregated volumes of 120 and April 140 and May 180. Then the results in percentages should be:

March: 120/120=100%

April: 140/120=117%

May: 180/120=150%

So using the fixed value of the summed volumes of Month March works just fine as long as you compare the sum of ALL volumes in a month. However, the volumes are categorized and it should be possible to make a selection in it. For ease of calculation say the volumes are spread as follows between category A and B;

March 70/30

April 60/40

May 65/35

With the fixed value, if I make the selection for category A, it would say:

March: (0,7*120)/120=70%

April: (0,6*140)/120=70%

May: (0,65*180)/120=98%

Whereas it should be:

March: (0,7*120)/(0,7*120)=100%

April: (0,6*140)/(0,7*120)=100%

May: (0,65*180)/(0,7*120)=139%

Hope this makes sense

Thanks!

Tom

Re: Use expression without limiting selection

qlikpahadi07 — Sun, 15 Dec 2013 11:19:31 GMT

I am sorry If i didn't get you properly again. you can attach a simple qvw which will help us to understand.

well I thing you can use Varaible where you can assign the value to varaible and divide it with your expression.

Let Var1 = Aggr(sum({<Object = {'ZZ0012'},MonthYear={'Mar-2012'}>}Volume),MonthYear)

so next time you can use ... exp/Var1

may be helpful