Hi every one,

I want to create a fromDate, toDate fields basing on an historical table, i have 3 date:

- effective date

- event date

- due date

here is my code :

policy_H_tmp:
NoConcatenate
LOAD policy_ID,
     date(EFFECTIVE_DATE,'DD/MM/YYYY') as EFFECTIVE_DATE,
     date(EVENT_DATE,'DD/MM/YYYY') as  EVENT_DATE,
     date(DUE_DATE,'DD/MM/YYYY') as DUE_DATE,
     CODE_INT,
    autonumber(CODE_INT,policy_ID) as rownumber_H
FROM  […….\policy.xls]  (ooxml, embedded labels, table is Sheet1);

LEFT JOIN(policy_H_tmp)

load 
policy_ID,
date(max(DUE_DATE),'DD/MM/YYYY') as MAX_DUE_DATE ,
max(rownumber_H) as rownumber_H
resident policy_H_tmp GROUP BY policy_ID;

policy_H:
NoConcatenate
load Distinct
policy_ID,
CODE_INT,
date(Min(date#(EVENT_DATE,'DD/MM/YYYY'))) as FromDtae,
date(date(MAX(if(isnull(MAX_DUE_DATE),EVENT_DATE,MAX_DUE_DATE +1)))-1)  as ToDate
resident policy_H_tmp  group by policy_ID,CODE_INT ,rownumber_H order by policy_ID;

drop table police_H_tmp;

with this code i found a problem in policy : 656470115000131  it  gives me fromDate -> 06/04/2015 toDate -> 06/04/2016 CODE_INT -> 5700, which is wrong it must show toDate -> 05/04/2017

i have joined my issue in attachment, the file named data represents my historical table while the other file is what i want,   can i do this with qlikview?

thank you for advance.

Hello guys,

I need help in calculating averages; pls have a look at my example: in the upper table I show all the participants to a championship and in the lower one I’d like to show the averages of them.

The  averages for every season must be calculated in this way: Season number of participants of each age / Number of races of the season

The  total averages must be calculated in this way: Total number of participants of each age / Total number of races of the seasons

I currently use this formula: Sum(PartecipantiRP)/Count(IdGaraRP)

But It’s wrong because Qlik considers only the races where has been at least one participant and not all the potential race for every season.

Should anybody help me with the right formula, pls?

Thanks.

N.

hello experts,

i am using qlikview 12 and for connect to our oracle database , i install odbc oracle . 

i connect to table of our oracle database and i can see columns same below picture:

but when i go to Preview tab for display fields values , no value display. same below picture:

and when i reload data, display below message:

My question is:

why i can connect to oracle table and view details of fields , but i can't view values of fields in Preview tab. and why display error 942 after reload?

thank you for your attention.

Hi,

I have a table with 4 Fields:

1- Brand

2- Product

3- Date

4- Stock

i want to create Condition task in nprinting 18 Nov, when the stock of any product from any branch on any date = 0,

Send me a report details (Brand, Product and Date).

Thank you

Hello Everyone,

I have a problem with an expression today . I think what Iwant to achieve is impossible but.. But maybe someone will have a magical idea 😉 ?

We sell a product  with a base on wich can be added options.

Information are stored in a database in 2 differents tables :

1 stored the SN (serial number of a system - unique indentifier of a system sold) with global options selected by the customer , Attention the customer can begin with Base system with or not options and in the future he can add Options, it will be still 1 line but add info to the SN : simplified exemple :

BASE 1 :

If I want the total of Option 3 in the market : answer is 6

Now I want have the number of this option 3 (or even system it is the same problem), sold per year or month, 

I have an other database , which store every Orders by SN and date.

So every time I have an entry it is a new line, If a customer update his system or add a new options :

Problem :

I joined the 2 tables with Qlik to chart my sales per year or month.

But If I make a chart to display the number of Options 3 or Systems per year for example (or month), The total through the years will be 11 instead of 6 because it will show every orders of Systems with the Option 3 for example. (If I want numbers of Option 1 instead by years, the real total is 3 but if I am charting it by years, it will display a total of 8 (because it will show every orders containing SN with option 1).

The only solution I came up with was to do something like :

To have Options 3 sold in 2012 for example

(Total of Options 3 from year 0 to year 2012) - (Total of Options 3 from 0 to 2011)

But this will give me number but I can't chart it anymore by using standard chart with Date as a dimension... Right ?

I know it is not easy to understand but if somebody has an idea :)... 

Hi.

I have an excel with 2 sheets:

1- Sales and Date.

2- Field 1(event)

     Field 2(DateFromCY)

     Field 3(DateToCY)

     Field 4(DateFromLY)

     Field 5(DateToLY)

     Field 6(Event Comparable)

     Field 6(DateFromComp)

     Field 6(DateToComp)

I want :

Column 1: example: Compare Sales event 20-1 Current year with Last Year same period.

Column 2: Compare event sales with the event comparable Sales ( Same row ),

                    example: Event 20-11  with  Event comparable 17-34.

Find the attached excel file.

Don't hesitate if you have any question.

Thank you.

Hi All,

Below are the some of details about dimension and measures are used in pivot table.

Dimension:

1.Bundle

2.Initiative

3.ProjectName

4.Category(Calculated Field)

Measures:

1.ceil(avg(Category_Percentage))

2.ceil(Avg(Category_Number))

i am getting wrong (avg of "Category_Percentage",avg of "Category_Number" ) at  Project ,Initiative ,Bundle level in some of the cases. In some of the cases i am getting correct (avg of "Category_Percentage",avg of "Category_Number") at all level that Project,Initiative,Bundle.

Note:All of the field used in pivot table  is coming from two table which is have mentioned in the attachment

Please find attached script  which i have written in the back end for two tables used in the pivot table.

Regards,

Madhavi 

Hi All

i have a combo chart with two dimensions on x axis. i want to include the line in the same chart as well.

but when i use the line option in chart properties it does not work. could some one please help to sort this out.

Hi All

My date field as below :-

Month

D/M/YY

I try below , it does not work :-

Date( Date#([Month], 'DD/MM/YY'), 'DD/MM/YYYY') as [date],

Hope some one can advise me.

Paul Yeo