How to get Straight table totals into textbox

[#1]

Hi all,

I want to see my totals of the Straight table into a textbox as an overview for the customer.

They want to see:

- How many customers are new in 2013 (this means, no Sales in 2012, Sales in 2013)

- How many customers don't have sales (omzet) in 2013 (Sales in 2012, no Sales in 2013).

- Increased Sales 2012-2013

- Decreased Sales 2012-2013.

When I build this into a Straight table this seams to be correct.

But when i put those expressions into text-boxes, something goes wrong.

Is this because some customers don't have values in 2012?

Can someone help me with this?

Thanks in advantage!

Fien

[bbi_mba_76]

Hi,

maybe you could add a flag if there are sales for the customer for each year and then use in the text boxes.

[#3]

Meanwhile, i think that i'm closer to the solution.

I was focussed on the totals of the chart, but i think that the totals of the charts of the increase/decrease are not correct.

if someone can help me out with this, that would be great.

In the attachment, you can find my new version of the qvw.

Kind regards,

Fien

[Anonymous]

Hi,

I tried to solve it, but did not see the solution. (I'm new to aggr and also set analysis is a bit complicated to me)

But I think we should narrow the problem with some smaller data(base).

I should say : max. 10 customers (each 2 a 3 invoices) and 2 increase, 2 decrease, 1 only in previous year and 1 only in current year.

I did discovered that, like you said, that it's going wrong when a customer has no invoices in the current year.

Paul

[#5 · Accepted solution]

hi,

use following expression

for increase=SUM({<Jaar=>}AGGR(if(sum(Omzet)>sum({<Jaar={"$(=Jaar-1)"}>}Omzet),1,0),Klantnummer))

for decrease =SUM({<Jaar=>}AGGR(if(sum(Omzet)<sum({<Jaar={"$(=Jaar-1)"}>}Omzet),1,0),Klantnummer))

[Carlos_Reyes]

Review the attached. I modified the straight table and text object formulas using Set Analysis instead of Aggr.

Hope the numbers are correct.

[Anonymous]

Hi,

Super, I think it's working.

Nandkishor, I did not yet compare the formula's, but could you give some comments on what was wrong ?

Until now I believed you could copy the formula from table to text-field, just add sum and that's it, but now ..

Paul

[Anonymous]

Hi,

Carlos,

This is not the solution !!  All clients who are new or lost, are wrong !!!

Nandkishor,

I did compare the formulas and now I see the solution, you dropt the year-selection by putting {<Jaar=>} in front of the AGGR-function !? 

Fien,

Don't forget to reward Nandkishor with a big bonus

Paul

[#9]

Hi All,

Nandkishor Pandirkar gives me the correct solutions. Many thanks for this!

In the attachment you can find the qvw with the correct formulas.

Thanks all for the input! You were great!