how can i show the total service no...while applying the date filter ....

hi Surya,

I'm a bit confused by the original question of filtering dates, but think this is actually a data blending problem?

My understanding is (correct me if I'm wrong)

PrLLSDBTab (JMSGPILOT) contains all service numbers

VuPD_CrTS2 (JMSGPILOT) contains all responded service numbers

and we want to blend the 2 to see the number of service numbers that were responded to. The problem comes from using Count Distinct on blends, which doesn't work like other aggregations. It can be done, but I'd advise you join them into a single datasource (you can do cross datasource joins, if they reside in different systems/sources). However he's how we can do it with blending.

The key to getting count distinct working on blends is that you need the field to be count distinct in the visual level of detail (vizLoD) for it to work.

So first I brought the Service Number (from PrLLSDBTab) into the VizLoD. We then need to use a table calculation to add them into a single sum.

I created this

[Service Number - All]

countd([Service No])

and the same for Responded in the VuPD_CrTS2 data source

[Service Numbers - Responded]

countd([Service No])

I also set the blend field on service number

Next for the Table Calculation versions...

[Service Number - All TC]

window_sum([Service Number - All])

and

[Service Number - Responded TC]

window_sum([VuPD_CrTS2 (JMSGPILOT)].[Service Numbers - Responded])

Now as we're making a pie chart, we don't want to double count them, so I created this...

[Service Number - Non Responded TC]

[Service Number - All TC]

-

[Service Number - Responded TC]

So now we have the right numbers...

but we have a copy for each service number, and we only want one.

So we do this...

[Service Number - Non Responded TC First]

if first()=0 then [Service Number - Non Responded TC] end

[Service Number - Responded TC First]

if first() = 0 then [Service Number - Responded TC] end

and this gives us this...

so only the first service number gets these values and the rest are Null.

We can then re-arrange into a pie chart....

As I said joining these into a single data source will make this much simpler.

Hope that helps