Nested calculation expressions and more

Tue, 17 Dec 2013 14:21:12 GMT

Hello QlikView community. Could someone please help me construct the calculation expression?:

Here is my data diagram:

I have a chart control (which looks like table) with dimension PublicationName so there can be many positions and positionHistories

And for each Publication in table I need to show the Count of Positions which have the positive difference of sums of their PositionHistoryValues for two points of time.

To select two points of time (a daterange) I use two variables vFromDate and vToDate and now I need to

(Sum(PositionHistoryValue) Where PositionHistoryDate == vToDate) -

(Sum(PositionHistoryValue) Where PositionHistoryDate == vFromDate)

this equation can tell me if this position is a winner (for selected daterange) or not.

I did this for QlikView like as follows ant it's working

=Sum({$<PositionHistoryDate={"$(vToDate)"}>}PositionHistoryValue) -

Sum({$<PositionHistoryDate={"$(vFromDate)"}>}PositionHistoryValue)

Now I need to count Winners and Loosers for each Publication. So if Publication has 5 Positions then 3 of them can be Winners and 2 Loosers for specific daterange.

I tried to use expression within expression but without no luck.

Here is it

=Count({$<Sum({$<PositionHistoryDate={"$(vToDate)"}>}PositionHistoryValue)-Sum({$<PositionHistoryDate={"$(vFromDate)"}>}PositionHistoryValue)={">=0"}>}PositionId)

I get an error "Error in set modifier expression"

Here is a link to QlikView file.

Could please someone turn me to the correct direction or give a great article to read?

As a next step I will have to do all above but filter positions by OpenDate and CloseDate.......

Re: Nested calculation expressions and more

Nicole-Smith — Wed, 18 Dec 2013 16:03:21 GMT

You'll need to use an if statement. From what I'm gathering, something like:

count(aggr(if(Sum({$<PositionHistoryDate={"$(vToDate)"}>}PositionHistoryValue)-Sum({$<PositionHistoryDate={"$(vFromDate)"}>}PositionHistoryValue)>=0, PositionId), PositionId))

Re: Nested calculation expressions and more

Gysbert_Wassenaar — Wed, 18 Dec 2013 16:57:07 GMT

Perhaps this:

=Count(if(aggr(Sum({$<PositionHistoryDate={"$(vToDate)"}>}PositionHistoryValue) -
               Sum({$<PositionHistoryDate={"$(vFromDate)"}>}PositionHistoryValue),
               AffiliateName,PublicationName)
          >=0,PositionId))

topic Re: Nested calculation expressions and more in QlikView

Nested calculation expressions and more

Re: Nested calculation expressions and more

Re: Nested calculation expressions and more