Help w Expression

[Anonymous]

Hello, I am a business user with no access to the scrip, using the IE plugin. I am trying to create an expression that Distinct Counts when the shipdate = newdate for the customer. The expression works correct when I have a month selected but when I look trended for the yr, the counts do not match previous months totals, like when I have a single month selected. The problem I am running into is if there are more then one shipdate, QV does not know to choose the first ship date to match to the new date and then does not get counted. I want QV to only count one time when the shipdate=newdate. I have tried min() around the Shipdate but not luck. Is there any other functions that I can add to my formula that would tell QV to look at the first shipdate to match to the newdate. Here is my formula: Thank you,

=Count(aggr(if(ShipDate=NEW_DATE,Count(DISTINCT Customer)),Customer))

[#2]

Hello Pdelvito,

I suggest you to create an expression similar to the following:

sum( if( aggr( nodistinct Min(ShipDate),Customer)= NEW_DATE,1 ,0) )

Note that I prefer summing up the '1' rather then using the count()-function. Hope it's (syntax-) bugfree because I can't test it.

HtH

Roland

[Anonymous]

Thanks for your help Roland. That did not work. I changed the nodistinct to distinct and that worked better. However it still shows incorrect when the table is trended 2011. Again when I am only selecting one month the numbers change and the formula works correctly. Do I need to reformat the date?

Any body else with ideas is greatly appreciated. Thanks,

[#4]

=count(distinct aggr(if(ShipDate=NEW_DATE,Customer),Customer,ShipDate))

I don't know what NEW_DATE is, but if there can be more than one NEW_DATE per customer I would add that like:

=count(distinct aggr(if(ShipDate=NEW_DATE,Customer),Customer,ShipDate,NEW_DATE))

[Oleg_Troyansky]

Folks, I have a feeling that we are trying to build something overly complex - all those IF and AGGR are not great for performance... I think there should be a simpler Set Analysis solution here.

What is NEW_DATE - is it a field or a variable? if it's a variable, perhaps something like this could work:

count( {<ShipDate = {$(NEW_DATE)}>} DISTINCT Customer)

[#6]

shipdate has to = NEW_DATE for the customer (for each customer?), so I don't think he can use set analysis b/c it's only going to return one thing per chart and not per row. If you figure out a way to do this with set analysis I'd love to know what it is.

[Oleg_Troyansky]

Dear Pdelvito,

please clarify your requirement:

- what kind of a chart is it?

- what are your dimensions in the chart?

- What is NEW_DATE - a single variable? a Field?

we need more information in order to recommend a good solution

[Anonymous]

Thank you both Trent and Oleg for your answers

Oleg: New date is a field. Set analysis only works when when I have a single Shipdate month selected and I need to view the report trended. This is why I moved away from set analysis. I also agree, if you can make it work with set analysis please let me know.

Trent: Your first formula worked perfectly as the numbers tied and the logic is correct. Now I also need to show the same critieria for revenue. I tried

=sum(Aggr(if(ShipDate=NEW_DATE,Rev),Rev,ShipDate))

But that did not work. Do you have any suggestions? Thank you again

[#9]

=sum(aggr(if(ShipDate=NEW_DATE,Rev),Customer,ShipDate))

You might need to change Rev to sum(Rev) but just try both and it should be obvious which one you want. Think of aggr as if you are making a chart. Would you want revenue as a dimension? Probably not. You would want Customer and ShipDate as dimensions so you should put those in the aggr.

[Anonymous]

Hi Oleg,

I am using a Pivot table. I have 2 dimensions: site and Shipdate month.

Hi Trent,

It worked Thank you very much. I did have to add the sum(Rev) to the formula and it is working. Thank you for the clarification on the Aggr Function.

Peter