10 Replies Latest reply: Jun 18, 2013 3:58 AM by Dimitar Prodanov

# Comparing periods

Hello Community,

If I need to monitor whether clients' sales totals this month are +or- 20% from last month and therefore only show those occurancies on my chart/table, is there a way to work with my data sources as they are?

'Visual clues' won't do, because I only want to show figures that fall into that criteria.

I'm thinking about doing this in the script, but because I have other different criteria to show as well, similar to the one above, I would have to create another 4 tables at least (the script already takes about 20minutes to run).

Thanks for any help.

• ###### Re: Comparing periods

You can do it using calculated dimensions.  That is, instead of dimension Client, it wiould be something like this:
aggr(if(fabs((sales2-sales1)/sales1)>=0.2, Client),Client)

You can use set analysis instead of "if", it depends on the specific situation anf yiur prefernces.

Regards,
Michael

• ###### Re: Comparing periods

Hello Michael,

Thanks for your answer. I was hoping I could get away without using aggr function :-)

Aggr and set analysis are a bit foggy for me at the moment as I am relatively new to QV. I'm learning...

I'll work on it, thanks again...

Regards,

Cristina

• ###### Re: Comparing periods

Trying to put together an aggr expression.

If I want to consider previous month and I have a Calendar set up, why doesn't

\$(=only(month)-1)

work??

I guess the other way to put it is: would you kindly give me an idea of the syntax for your aggr function given:

sales1 = tot sales selected month

sales2 = tot sales (selected month - 1)

Cristina

• ###### Re: Comparing periods

Cristina,

That could be because of the format issue. Try something like:

=

Sum({\$<MonthField={'\$(=Month(MakeDate(1,MonthField-1,1))'}>} Amount)

Update: MakeDate():

Thanks.

• ###### Re: Comparing periods

Hello tresesco,

I've tried your expression, but unfortunately it's not returning any value...

So I've changed it to:

Sum

({\$<Month={'\$(=Monthname(MakeDate(only(Year),only(Month)-1))'}>}Sales

)

but it doesn't work either. If I insert a text box with

=

Monthname(MakeDate(only(Year),only(Month)-1))

the value returned is the month before the one I selected, which is right. I just need to have that as my modifier...

Thanks,

Cristina

• ###### Re: Comparing periods

It took a while but I did work on it and thank you! That worked perfectly!

• ###### Re: Comparing periods

Hi Michel

Can we use aggregation functions like sum,avg in calculated dimensions?

Regards

Murali krishna

• ###### Re: Comparing periods

Hi

You can use aggregated function, but must use function aggr. When use it in dimension syntax is aggr(sum(<field>),<group by fields>)

Regards

Dimitar

• ###### Re: Comparing periods

Thanks Prodanav

Can't we directly use aggregated functions without using function aggr?

Because in my requirement no need to perform aggregation based on fields

Regards
Murali krishna

• ###### Re: Comparing periods

Hi

Can you attach an exam application? I don't understand a requirement.

Regards

Dimitar