How to perform a calculation without using sum

[#1]

Hi,

In my QVW one of the fields named sales has a value which is correct for each week if i take it in a text box, if i use a text object and do SUM(Sales) what happens is instead of calculating the sales for the given filters its calculating the sales for everything because the sales is already aggregated before coming into this QVW.

If i pick a certain date and in the text object if i do Sales i get the exact value but if i do sum(sales) it gives me everything.

The problem with this is, if i pick multiple dates the text box gets blanked out as i am not doing any aggregation operation.

I could have posted my QVW but its too large.

Can someone please throw any suggestions on this?

Thanks.

[Anonymous]

Hi,

Can you use function only

[#3]

Hi Nita,

The problem with only function is it works fine if i just select one date , but if i select multiple dates then it returns blank same as just the sales.

Any suggestions?

Thanks

[#4]

Would it be possible for you to post the QVW with only a sample amount of data so we could get an idea of the behavior you're describing?

[hic]

If I understand you correctly, you have a non-additive number? If you pick any date, you will get a relevant number. But you can't sum the number over several dates.

If so, you should use some other aggregation function than Sum(). Suggestions:

- Avg(Aggr(Sum(Sales),Date)) = Sum(Sales) / Count(distinct Date)

     This sums within each date, and averages over dates.

- FirstSortedValue(Aggr(Sum(Sales),Date),-Date)

     This sums within each date, and picks the last date.

HIC

[#6]

Hi,

I have attached the reduced version and if you seee the blue text object i have my script in it and it works fine for 1 date but if i select multiple dates it gets blanked out.

Can you please look into it and let me know any suggestions.

Thanks

[Anonymous]

ok.

Can you provide some details. It will be really nice if you can reproduce the issue in a .qvw

[#8]

Hi Nita,

I have attached my QVW below which has the issue.

Thanks

[#9]

Hi Henric,

I tried both of your conditions and nothing worked out for me.

Thanks

[hic]

My point is that you cannot use Only(). Then you will get exactly this behaviour.

I don't know exactly how you want to perform your calculation, but both the following expressions give results that look OK:

=Avg({<TransactionChannelDesc ={POS},TransactionTypeId = {1}>}[Transaction Count])

=FirstSortedValue({<TransactionChannelDesc ={POS},TransactionTypeId = {1}>}[Transaction Count], [Date Id])

But, since you have several transactions per date, I suspect you need an Aggr() function. How should such cases be handled? Sum within a day and then average over the different days, or?

HIC