topic Re: Comparing periods in QlikView

Comparing periods

Wed, 12 Jun 2013 00:40:29 GMT

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

Anonymous — Wed, 12 Jun 2013 01:03:02 GMT

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

Wed, 12 Jun 2013 01:30:32 GMT

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

Wed, 12 Jun 2013 05:06:20 GMT

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

Re: Comparing periods

tresB — Wed, 12 Jun 2013 05:17:59 GMT

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

Thu, 13 Jun 2013 01:06:32 GMT

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

Tue, 18 Jun 2013 02:53:35 GMT

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

Re: Comparing periods

Tue, 18 Jun 2013 06:18:29 GMT

Hi Michel

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

Thanks in Advance

Regards

Murali krishna

Re: Comparing periods

prodanov — Tue, 18 Jun 2013 06:25:47 GMT

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

Tue, 18 Jun 2013 06:37:32 GMT

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

prodanov — Tue, 18 Jun 2013 07:58:11 GMT

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

Regards

Dimitar