Aggr problem

[#1]

Hi QlikView community,

I have got a problem with Aggr using.

I link to this mail a QVW example and this is some informations about this case.

I have got 3 sources : TVA, TVA per distance, and a central table with some value and a date.

• TVA give me informations about a TVA value depending of Country / Start Date and End Date.

//TVA per country and date, example

// Country  TVA     start date      end date

// France   10%      01/01/2010  01/01/2013

// France    7%      01/01/2013  01/01/2015

// Belgium   6%      01/01/2010  01/01/2015

• TVA per distance give me a % depending of a country and a journey.

//Example, % by country of a journey

// IDStation              Country    TVAShare

// Paris - Brussels    France     70%

//  Paris - Brussels   Belgium     30%

//  Berlin - Brussels  Germany     30%

//  Berlin - Brussels  Netherland   30%

//  Berlin - Brussels  Belgium    40%

Central table give me an amount payed by different passenger for a journey

//Example

// Passenger    IDstation       SegmentSTD    CA_transport_PAX 

// 1             Berlin - Brussels     05/04/2012           120

// 2              Paris - Brussels     07/04/2012            99

My goal is to calculate, per Country, a sum of this amounted * % per country * TVA value where my value date is between the start date and the end date.

It's OK when build a table with all date and TVA informations,

but it is not when my analyse have only country dimension.

I try to use this kind of expression, but without success :

if((SegmentSTD)>(START_DATE) and (SegmentSTD)<(END_DATE), sum(aggr(sum(CA_transport_Pax)*TVA....,CountryCode,IDstation,START_DATE,END_DATE)))

//Country      Amout

//Belgique 120 euros *TVA_share (40% of Berlin Brussels ) * TVA (6%)    +     99 euros *TVA_share (30% of Paris Brussels ) * TVA (6%)

I give some explications in the script of the example, with extract of the table and goal waiting.

Thank you for your help,

Mickaël.

[#2]

Hi Mickael

you could to use the intervalmatch() function in your script to match the passengers journeys to the date range given in the TVA per country and date example.

This function will create a link table between your  table_centrale and Tva (with dates table) that only links when the passendger journey date is within the timeframe

eg

intervalmatch (SegmentSTD) Load start_Date, End_date resident tva;

This creates a link table between the two

Erica

[#3]

Once you've done this of course, the expression will be easy in the application in QV. You would literally just * the items together, instead of needing complicated if statements

=TVA_VALUE*TVA_SHARE*ca_transporT_pax

Erica