Impromptu distinct count summary

[monsky]

Helloooooo experts,

How do I create a distinct count summary on a report with several rows of disinct count subtotals? I know this can be done with PowerPlay Transformer through roll-ups but what about in Impromptu?

 

[tinmar]

you need to use

select sum

of the table name you are after.

 

[DoubleD]

In your report footer, you create another Distinct Count field, then in your data window you associate that calculation to the Report level.

Another option is:
From the report view, click on the column you want to count.
Click the Count button to create counts at all grouped levels. Remove the groupings you don't want, then modify the calculation at the Report level to include the word Distinct.

Pain is stress leaving the body.

DoubleD

 

[monsky]

This should be the distinct count values

Policy Owners Policies
------------- --------
185862 205651

This is what I'm getting

Currency Policy Owners Policies
Dollar 1,647 1,850
Peso 184,259 203,802
----------------------------------------
Distinct Total 185,906 205,652
----------------------------------------


I tried using the total(distinct Policy Owners) but still get the same result.

 

[DoubleD]

I'm confused, do you want currency included or not?

If you want your results to look like this:

Currency Policy Owners Policies
Dollar 1,647 1,850
Peso 184,259 203,802
--------------------------------------------
Distinct Count 185,682 205,651

Then you need two calculated fields:
Distinct Owners: COUNT( DISTINCT Policy Owner)
Distinct Policies: COUNT( DISTINCT Policy Number)

If you want your results to look like this:

Policy Owners Policies
185,682 205,651

First you need to remove Currency as a grouped field.
Then you need two calculated fields:
Distinct Owners: COUNT( DISTINCT Policy Owner)
Distinct Policies: COUNT( DISTINCT Policy Number)


Pain is stress leaving the body.

DoubleD

 

[stewm]

Do you want sum(value) or sum(distinct value). Group your value you're subtotalling and it will give sum(distinct value).

Mark Stewart
Senior Analyst
Consultants Club Corp.
Windsor, Ontario
Canada

http://www.consultantsclub.biz

 

[monsky]

I'm just beginning to understand how Impromptu creates this kind of reports. When I group the policies and policy owners and have a distinct count for each currency there only one query built by Impromptu. Since I cannot do a direct sum since this will no longer create a distinct summary so I used DoubleD's suggestion. This worked now. So I've actually got two queries when I generate the report. One is for the main report while the second query is for the summary.

Correct me if I'm wrong.

The only concern I have right now is performance issues. Unless I come across another stumbling block. Thanks guys.

 

[Nunina]

Did you solve it, monsky? I'm having sort of the same problem...





Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[monsky]

Hey Nunina,

Well it did work out. I'm guessing that there's just no other way to do it but create a main query for currency grouping with distinct counts, create a count summary for the policy and policy owner column, then modifying the data description on each count summary by changing the level from the currency level to the report level. By doing this Impromptu will generate a second SQL statement specifically for the distinct count summary.

If you do a distinct counting on currency level it will return the number of rows.

I'm just figuring out how to improve the query performance since this involves multiple queries.

 

[DoubleD]

You're asking for the data to be grouped and give you distinct counts at the group level. Then your asking the database to give you a distinct count of all your records.

Unless you change the way the data is stored, you will need two SQL statements to accomplish your results.


Pain is stress leaving the body.

DoubleD

 

[monsky]

How would I change the way my data is stored? I have a table of policies and a table of policy owners. The only time I won't have two (or more queries) is if I only have group summaries.

 

[DoubleD]

If this is data you use often, you could create a summary table or a materialized view to "precalculate" your data.
You're other option is to have these as separate IQD files. They should run pretty quickly unless you have a large amount of data.

Pain is stress leaving the body.

DoubleD

 

[monsky]

In my case I don't think I have a choice. I need details since the status of policies can change anyday. I could let our ETL tool do the precalculation so that my report comes out faster but no can do in my case.

 

[monsky]

Follow through:

Ok, I may have the idea on how to work on this problem. My question now is what if you already have this embedded in a crosstab and there are already several groupings involved? I'm not sure if it will still give me the right answer or that things will still be simple enough to do it right.

 

[DoubleD]

I don't know that you're going to gain any performance by changing this to a crosstab. I would think that ultimately it is still two SQL statements. Is the performance of your report poor enough to justify this work?

Pain is stress leaving the body.

DoubleD

 

[monsky]

Sorry about that. I should have clarified that the report that I'm building has evolved from the simple report that I shown you guys before.

 

[monsky]

Ok, here I go again. I'm trying to perform a count distinct summary on a customer count in a crosstab report. I cannot perform a summary calculation with the crosstab dialog box because it will not give me the right summary count. Even if I do it manually the count will only return the number of rows. If I attempt to edit the data definition of the distinct count by using 'for' and using ('Business + Currency + Market) an error is generated.

Now how the heck do I do this?

 

[DoubleD]

You may have to use two separate calculations in your report. The first: Count(Policy Owner) will give you the total number of Policy Owners. The second: Count(Distinct Policy Owner) will give you the distinct count of Policy Owners.
I don't know of any way to show the actual count in the crosstab, but have the summary level be a distinct count.

Pain is stress leaving the body.

DoubleD