How to add summing the field based on certain criteria

[Draszor]

Hi,

I need your help with the following:
I have a database (attached) with AllData Query. I would like to add few other numeric dimensions to this query, in the way shown in attached excel. Could you please help me in it? This seams to be very easy, cant be diffficult if in excel it is basically one formula sumifs... but I failed so far in having it done

Thanks a lot for any help



//Draszor

[June7]

Looks like you need to build a report with the AllData query and use report Grouping & Sorting with aggregate calcs functionality.

Otherwise, what you want will require some complex DSum function calcs or multiple queries to accomplish.

[Draszor]

Looks like you need to build a report with the AllData query and use report Grouping & Sorting with aggregate calcs functionality.

Otherwise, what you want will require some complex DSum function calcs or multiple queries to accomplish.

Can you attach some example? like adding one discount to my DB, so that I could follow..?

[June7]

Review http://office.microsoft.com/en-us/ac...010006894.aspx

[Draszor]

Thanks a lot. I will definitelly review this.

There is a problem however with such set-up...

I am using a different software (QlikView) to built the presentation form (charts/tables/quick and easy filtering) out of my Access Data. So, access file should be just a database, nothing else... The problem is , that QlikView can expoer tables, queries BUT NOT REPORTS from access... so I need to create a query, because with report it won't work...

[June7]

Okay, might be able to accomplish with domain aggregate functions (DSum, DCount, DAvg, etc). However, they can make queries run slow. But I tested calcs and they ran fast on this dataset. Example:

12_VD: DLookup("[12_VD]","[DedVDT]","[VDCustomer]='" & [VDCustomer] & "' AND [VD_Type]='" & [VDType] & "'")
13_VD: DLookup("[13_VD]","[DedVDT]","[VDCustomer]='" & [VDCustomer] & "' AND [VD_Type]='" & [VDType] & "'")

Enter that code in cells of Field row with the AllData query in Design view. To show as percent:
12_VD: Format(DLookup("[12_VD]","[DedVDT]","[VDCustomer]='" & [VDCustomer] & "' AND [VD_Type]='" & [VDType] & "'"),"Percent")
13_VD: Format(DLookup("[13_VD]","[DedVDT]","[VDCustomer]='" & [VDCustomer] & "' AND [VD_Type]='" & [VDType] & "'"),"Percent")

You have VDCustomer values that are not in DedVDT table so some of these calcs will be null result. Why are the values in 12_VD and 13_VD fields the same?

QlikView can import from Access? Can it import from Excel?

[Draszor]

Thanks a lot. I will try this today definitelly.

QlikView - yes, it can import from any data base - starting from .txt .csv files, via excel, access, SQL finishing with ERP platforms like SAP...
Wonderful program, I am really fan of it - very flexible, very user friendly (herer I mean the final user of the report).

[Draszor]

I checked your formula with one discount and it works perfectly, I am really grateful for your help... I needed to modify it a little bit, so finally it is:

12_VD: Format(DLookUp("[12_VD]";"[DedVDT]";"[DedVDT.VDCustomer]='" & [CustomerT.VDCustomer] & "' AND [VD_Type]='" & [VDType] & "'");"Percent")

but it works and this is it... FINAL QUESTON... WHAT NOW WOULD BE THE SIMPLEST WAY TO MODIFY THIS FORMULA SO THAT THE ZERO VALUE IS ENTERED IN ALL RECORDS HAVING NOW NULL VALUE FOR 12_VD COLUMN ..?

[June7]

Try:

12_VD: Format(Nz(DLookup("[12_VD]","[DedVDT]","[VDCustomer]='" & [VDCustomer] & "' AND [VD_Type]='" & [VDType] & "'"),0),"Percent")

[Draszor]

Thanks a lot... so simple... ;-))