topic Re: How to do i do these set analysis in QlikView

How to do i do these set analysis

Mon, 15 Feb 2016 16:57:38 GMT

See this set analysis. I have 12 fields Jan.... Dec with values. how do i pick all these months in one expression?

Sum({<Year={2013}> Jan, Feb, Mar.... Dec ) How do i do this?

Re: How to do i do these set analysis

sunny_talwar — Mon, 15 Feb 2016 16:58:57 GMT

May be this:

Sum({<Year={2013}> (Jan+Feb+Mar+.... +Dec))

RangeSum(Sum({<Year={2013}> Jan), Sum({<Year={2013}> Feb), Sum({<Year={2013}> Mar), ....., Sum({<Year={2013}> Dec))

Re: How to do i do these set analysis

cesaraccardi — Mon, 15 Feb 2016 17:03:14 GMT

Hi,

You might want to consider changing the data model of your application to a vertical model rather than having a field for each month, that could possibly make your life a lot easier. QlikView has a CrossTable loading command specifically for this kind of dataset.

Regards,

Cesar

Re: How to do i do these set analysis

Colin-Albert — Mon, 15 Feb 2016 17:03:32 GMT

Would it be better to change your data model so the Month is a dimension and you just have one measure field?

Then you can select months in a list box along with year and use a simple expression such as sum(value) or

sum({<Year = {2013}>} Value)

Re: How to do i do these set analysis

Mon, 15 Feb 2016 17:07:39 GMT

Didnt work

Re: How to do i do these set analysis

Colin-Albert — Mon, 15 Feb 2016 17:08:04 GMT

As CesarAccardi‌ suggests, look at the Cross Table function in your load script, and avoid having 12 value fields.

The Crosstable Load

Re: How to do i do these set analysis

Mon, 15 Feb 2016 17:09:51 GMT

What i wanna achieve dont need the data model to be changed

Re: How to do i do these set analysis

Chanty4u — Mon, 15 Feb 2016 17:11:54 GMT

Hi,

You can do this by following way.

Tab1:
SQL
Select Date,Month(Date) as Months,Sales From yoursqltable

Months:
Load * INLINE [
Months, Month
01, Jan
02, Feb
03, Mar
04, Apr
05, May
06, Jun
07, Jul
08, Aug
09, Sep
10, Oct
11, Nov
12, Dec
];

After reloading you qv document create the new list box and select the "Month" field on this list box.

Then your list box will display Jan,Feb,Mar,...,Dec.

Re: How to do i do these set analysis

Chanty4u — Mon, 15 Feb 2016 17:12:19 GMT

DUAL(Month(YourDate), MonthName(YourDate)) AS Month

Re: How to do i do these set analysis

Colin-Albert — Mon, 15 Feb 2016 17:13:24 GMT

You can do it without changing the data model, but you will need to sum 12 expressions.

Sum({<Year={2013}> Jan) + Sum({<Year={2013}> Feb) + ... + Sum({<Year={2013}> Dec)

A well structured data model will be more efficient going forward.

Re: How to do i do these set analysis

Mon, 15 Feb 2016 17:16:04 GMT

I rephrased and it worked

Re: How to do i do these set analysis

Mon, 15 Feb 2016 17:52:19 GMT

How do i resolve this? didnt work as i tot it would i wanna get total for the Totals i defined