topic Re: Struggling with Set Analysis in QlikView

Struggling with Set Analysis

Sat, 25 Jan 2014 12:58:39 GMT

Hello,

I am struggling with something and would need help.

I have a table with shows PLAN and ACTUAL SALES for Financial Year 2014.(which starts from Sept 2013)

Now when I click on year it sums sales (i.e. 😎 and sums plan (i.e. 43) .... but I want to find a way so that it sums plan only for the month where we have a sales numbers i.e. it should only sum plan = 2+3+4+3 ....

Not sure how to find out the month which have actual sales.

Month '14	Plan($) ' 14	Actual Sales($) '14
Sep	2	2
Oct	3	3
Nov	4	2
Dec	3	1
Jan	2
Feb	3
Mar	4
Apr	5
May	6
Jun	4
Jul	3
Aug	4
Total	43	8

thanks in advance

Re: Struggling with Set Analysis

swuehl — Sat, 25 Jan 2014 13:05:47 GMT

Maybe like

=sum({< [ACTUAL SALES] = {">0"}>} [PLAN])

Re: Struggling with Set Analysis

nilesh_gangurde — Sat, 25 Jan 2014 13:07:42 GMT

Hi,

you can write the following expression:

Sum( {<Date_Field = {"=not isnull(Actual Sales)"} >} PLANS)

-Nilesh

Re: Struggling with Set Analysis

rustyfishbones — Sat, 25 Jan 2014 13:09:21 GMT

Try this

Re: Struggling with Set Analysis

Sat, 25 Jan 2014 15:13:19 GMT

Thanks guys,

Sales have negative values ... so >0 logic not returning correct answer

Nilesh,

Your logic, returns 0, so it is not summing up anything

Re: Struggling with Set Analysis

Sat, 25 Jan 2014 15:16:03 GMT

=sum({$<[Sales Actual]={"=not isnull(Actual Sales)"}>}[Plan Value])

This is what I have tried

I also added empty rows in sales for the remaining months.

Re: Struggling with Set Analysis

Sat, 25 Jan 2014 15:30:46 GMT

i have attached the file

Re: Struggling with Set Analysis

MK_QSL — Sat, 25 Jan 2014 16:36:36 GMT

Please check enclosed file...

Re: Struggling with Set Analysis

Sat, 25 Jan 2014 16:58:26 GMT

Thanks Manish.

It works.

Will be even great if you can explain the dimension you have added

=IF(AGGR(SUM([Sales Actual]),Month)>0,Month)

thanks buddy!

Re: Struggling with Set Analysis

MK_QSL — Sat, 25 Jan 2014 17:04:56 GMT

AGGR(SUM([Sales Actual]),Month

This will give you SUM([Sales Actual]) group by Month

IF(AGGR(SUM([Sales Actual]),Month)>0..... will check if SUM([Sales Actual ]) greater than 0 then only it will take

Month as Dimension...

Re: Struggling with Set Analysis

Sat, 25 Jan 2014 17:18:07 GMT

Manish,

Can we somehow replace >0 by isnotnull or something?

As sales can be -ve

Re: Struggling with Set Analysis

MK_QSL — Sat, 25 Jan 2014 17:26:41 GMT

Haven't tried but may be something like below...

=IF(AGGR(SUM([Sales Actual]),Month)>0 and Not ISNULL(AGGR(SUM([Sales Actual]),Month)),Month)