Comparing periods

[#1]

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.

[Anonymous · Accepted solution]

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

[#3]

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

[#4]

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)

Thanks in advance.

Cristina

[tresesco]

Cristina,

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

=

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

Update: MakeDate():

Thanks.

[#6]

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

[#7]

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

[#8]

Hi Michel

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

Thanks in Advance

Regards

Murali krishna

[prodanov]

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

[#10]

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