Aggr problem

Wed, 26 Feb 2014 10:32:02 GMT

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.

Re: Aggr problem

Wed, 26 Feb 2014 13:55:26 GMT

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

intervalmatch (SegmentSTD) Load start_Date, End_date resident tva;

This creates a link table between the two

Erica

Re: Aggr problem

Wed, 26 Feb 2014 13:57:00 GMT

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

topic Re: Aggr problem in QlikView

Aggr problem

Re: Aggr problem

Re: Aggr problem