topic Set Analysis Question in QlikView

Set Analysis Question

Wed, 13 Nov 2013 06:52:46 GMT

Hi All,

I am pretty new to Qlikview and have come across a unique requirement.

I have to get sum of sales made against a customer for a product. If the user selects a time period (which is in months) say Jan 2013 then the sales of the product sold to the customer in month Jan 2013 should be shown. However along with this I also have to show the sales which happened against the same customer for the same product in Jan 2012.

To achieve the above part I had used the below expression and I am able to get the result since my Month_Key is in format of YYYYMM.

sum({$<Month_Key = {$(=max(Month_Key) - 100)}>}Sales)

Earlier the requirement stated that the user will select only one month. This has changed. User should be able to select multiple months now and this makes my above expression useless.

Now based on the months selected by the user I have to provide the sum of sales of those months for current year and last year.

Will I have to use variables to get the sales of last year for the selected months? Is there a simpler way to do it?

I tried searching but was not able to find any material on this.

Any help will be really appreciated.

Thanks,

Prakhar

Re: Set Analysis Question

kaushiknsolanki — Wed, 13 Nov 2013 07:00:02 GMT

Hi,

Could you please share the sample data.

Regards,

Kaushik Solanki

Re: Set Analysis Question

Wed, 13 Nov 2013 07:11:49 GMT

Hi,

You can use variables in the set analysis, each one for one month and therefore you will be able to subtract the desired month to the current one.

You can create a variable vCurrentMonth (or vMaxMonth), vPreviousMaxMonth .... and so on.

Then you can use the following formula

sum({$<Month_Key = {$(vPreviousMaxMonth )}>}Sales) , .... depending on each case.

Regards

Re: Set Analysis Question

Wed, 13 Nov 2013 07:23:01 GMT

Hi Kaushik,

I am preparing the Sample data and will send in sometime.

Hi Jamie,

Thanks for your reply however what happens if user selects multiple months say JAN 2013, FEB 2013, MAR 2013 together. My issue arises when i have to get the sum of sales of JAN 2012, FEB 2012, MAR 2012.

Thanks for helping!

Prakhar Vyas

Re: Set Analysis Question

Wed, 13 Nov 2013 07:35:00 GMT

Hi Prakhar,

In this case I will use some like the following formula:

sum({$<Month_Key = {">=$(=min(Month_Key) - 100)<=$(=max(Month_Key) - 100)"}>}Sales)

or in 2 steps:

sum({$<Month_Key = {">=$(=min(Month_Key) - 100)"}>}Sales)

sum({$<Month_Key = {">=$(=max(Month_Key) - 100)"}>}Sales)

Regards

Re: Set Analysis Question

Wed, 13 Nov 2013 08:12:06 GMT

Hi Jamie,

Thanks a lot!

That worked like a charm:)

Kind Regards,

Prakhar Vyas