Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Avoid to expand data to work with dates

Good morning, I am looking for a piece of script about “master calendars” or a similar solution to the following problem:

My BBDD data source generates rows or records like these  (date format dd/mm/yy):

HOTELMMCDOTOTTOOIDCTOFIPEFFPETHABASE
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA75
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA77

Where the Price (BASE) is 75 y 77 for the periods included between FIPE (Begining Period Date) and FFPE (Ending Period Date) respectively, that means, for the first record or row the valid price (BASE) is 75 during 8 days and for the second record the valid price is 77 during 20 days.

Summarizing, in order to analyze Price averages for  differents periods etc the only way I found was to join a new column called FECHA (DATE) and transformate the original BBDD table in this other:

HOTELMMCDOTOTTOOIDCTOFIPEFFPETHABASEFECHA
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7501/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7502/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7503/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7504/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7505/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7506/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7507/01/2012
1ALEALEMANIAAIMTHOMAS COOK AG (AIM)8213AIMTULS1201/05/201208/05/2012HA7508/01/2012
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7701/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7702/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7703/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7704/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7705/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7706/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7707/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7708/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7709/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7710/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7711/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7712/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7713/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7714/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7715/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7716/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7717/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7718/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7719/11/2011
1ALEALEMANIABUCHERTHOMAS COOK AG (BUC)3614BUCHERTULW1101/11/201120/11/2011HA7720/11/2011

This way I can deal with dates, data and formulas with total flexibility thanks to the new field FECHA (DATE), but is unbearable for access or excel.

Could please anyone suggest me a solution or advice in QV script code to work with dates without having to expand the BBDD data source?

Thank you

1 Solution

Accepted Solutions
Employee
Employee

Re: Avoid to expand data to work with dates

You need to expand the date ranges, but you do not need to join the date ranges onto the BBDD table:

BBDD:

LOAD * FROM [ejemplo.xlsx]

(ooxml, embedded labels, table is [DATA_CON_20120905-172847])

          where recno() <= 2;

DateRanges:

Load distinct

          FIPE,

          FFPE,

          Date(FIPE + iterno() -1) as FECHA

          resident BBDD

          While iterno() <= 1 + FFPE - FIPE;

Change the first LOAD to fit your real data. (Remove the "where recno() <= 2" clause which is just needed for your example file.) You will get a data model with a synthetic key, but this is not a problem - it is instead the most efficient data model.

HIC

13 Replies
Employee
Employee

Re: Avoid to expand data to work with dates

You need to expand the date ranges, but you do not need to join the date ranges onto the BBDD table:

BBDD:

LOAD * FROM [ejemplo.xlsx]

(ooxml, embedded labels, table is [DATA_CON_20120905-172847])

          where recno() <= 2;

DateRanges:

Load distinct

          FIPE,

          FFPE,

          Date(FIPE + iterno() -1) as FECHA

          resident BBDD

          While iterno() <= 1 + FFPE - FIPE;

Change the first LOAD to fit your real data. (Remove the "where recno() <= 2" clause which is just needed for your example file.) You will get a data model with a synthetic key, but this is not a problem - it is instead the most efficient data model.

HIC

Not applicable

Re: Avoid to expand data to work with dates

Thank you very much, it is exactly what I was looking for.

One more question about this please. I think the function "=Avg(BASE)" is giving me the amount of (75+77)/2=76. How could I get a ponderate average according to the FECHA range I could select in designer dynamically?

For example, if I am analysing a period FFPE-FIPE=24 involving,

4 days for 75 ,

8 days for both prices

and 12 days 77

Where the right result should be:

(75x4 + ((75+77)/2)x8 + 77x12) = 76,33333

Thanks

Not applicable

Re: Avoid to expand data to work with dates

Thank you very much, it is exactly what I was looking for.

One more question about this please. I think the function "=Avg(BASE)" is giving me the amount of (75+77)/2=76. How could I get a ponderate average according to the FECHA range I could select in designer dynamically?

For example, if I am analysing a period FFPE-FIPE=24 involving,

4 days for 75 ,

8 days for both prices

and 12 days 77

Where the right result should be:

(75x4 + ((75+77)/2)x8 + 77x12) = 76,33333

Thanks

Not applicable

Re: Avoid to expand data to work with dates

Thank you very much, it is exactly what I was looking for.

One more question about this please. I think the function "=Avg(BASE)" will give me the amount of (75+77)/2=76. How could I get a ponderate average according to the range of FECHA I could select in designer dynamically?

For example, if I am analysing a period FFPE-FIPE=24 involving,

4 days for 75 ,

8 days for both prices

and 12 days 77

Where the right result should be:

(75x4 + ((75+77)/2)x8 + 77x12) = 76,33333

Thanks

Not applicable

Re: Avoid to expand data to work with dates

Thank you very much, it is exactly what I was looking for.

One more question about this please. I think the function "=Avg(BASE)" will give me the amount of (75+77)/2=76. How could I get a ponderate average according to the range of FECHA I could select in designer dynamically?

For example, if I am analysing a period FFPE-FIPE=24 involving,

4 days for 75 ,

8 days for both prices

and 12 days 77

Where the right result should be:

(75x4 + ((75+77)/2)x8 + 77x12) = 76,33333

Thanks

Employee
Employee

Re: Avoid to expand data to work with dates

In your example, BASE is found in a table with only two records. Then QlikVIew will calculate the average over these two records, i.e. Avg(BASE) = (75+77)/2

If you want to make the summation in the other table, then you need to get this field into this table. Just join the two tables (put the word "Join" in front of the second load) and you will have one table with 28 records. Now QlikView will calculate the average to (75*8+77*20)/28 which is almost what you want.

But it seems to me as if you want each day (not each record) to have the same weight, or? If so, you must use a nested aggregation - first calculate the average for each day, then average over the days. You should use the formula Avg(Aggr(Avg(BASE),FECHA))

HIC

Not applicable

Re: Avoid to expand data to work with dates

Certainly make the join load improve my understanding of the model. With the field 'FECHA' as a part of the main and unic table I think that a simple =avg(BASE) is producing right results except when I choose a FECHA range with no Price, in this case the average include ceros. ¿Is there any way or parameter to get an average without including ceros?

In the attached example the righr result would be average = 22,87  instead of 17,533 as a result of considering 23 BASE values <>0 instead of 30.

Thank you

Employee
Employee

Re: Avoid to expand data to work with dates

If you want to exclude zeros, you could use

Avg(if(BASE<>0,BASE))

or

Sum(BASE) / Count(if(BASE<>0,BASE))

HIC

Not applicable

Re: Avoid to expand data to work with dates

Perfect, now I think I have to create a master calendar linked to FECHA in order to compare months of different years an so on.

Thank you very much.

Community Browser