Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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
Cristina,
That could be because of the format issue. Try something like:
=
Sum({$<MonthField={'$(=Month(MakeDate(1,MonthField-1,1))'}>} Amount)Update: MakeDate():
Thanks.
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
It took a while but I did work on it and thank you! That worked perfectly!
Hi Michel
Can we use aggregation functions like sum,avg in calculated dimensions?
Thanks in Advance
Regards
Murali krishna
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
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