topic Re: Num of colum with and without data in QlikView

Num of colum with and without data

Fri, 11 Apr 2014 06:27:00 GMT

I need to reproduce the execel that i atach.

I need know hoe to calculate Num Month, and Nun Month data

Basically num month will count num of month by year, and num month data count the num of month dhata have data.

Thanks in advance.

Re: Num of colum with and without data

MK_QSL — Fri, 11 Apr 2014 06:50:33 GMT

How's your actual data. If they are as per the excel file, it's difficult to work.

Re: Num of colum with and without data

sujeetsingh — Fri, 11 Apr 2014 06:50:40 GMT

Explain in detail plz

Re: Num of colum with and without data

Fri, 11 Apr 2014 06:54:29 GMT

I HAVE MY DATA IN A ORACLE TABLE.

TO BE SIMPLE I HAVE IN A PIVOT TABLE.

DIMENSION.

YEAR

MONTH

CONTACT

AND EXPRESSION

SUM(SALES1 +SALES2 + SALES3)

I can do all the excel expect to column i am asking. Num of month and num of month with data.

Regards

Re: Num of colum with and without data

MK_QSL — Fri, 11 Apr 2014 06:55:47 GMT

Can you provide data in excel file as per mentioned above...

Re: Num of colum with and without data

er_mohit — Fri, 11 Apr 2014 07:02:39 GMT

Try this code in text object

here, my blank field name which having value also is Data

=count(if(len(Data)>0,Data))

or to count num month

Try this

=count(Month)

Re: Re: Num of colum with and without data

Fri, 11 Apr 2014 07:13:43 GMT

Here the example.!!

Thanks!

Re: Num of colum with and without data

bertdijks — Fri, 11 Apr 2014 07:22:44 GMT

For the number of months with data:

=NumericCount(data)

for the number of months in year

=count( MONTH)

Re: Re: Num of colum with and without data

Fri, 11 Apr 2014 07:29:39 GMT

how do you put in that specific side in the pivot table?

Re: Num of colum with and without data

mato32188 — Fri, 11 Apr 2014 07:34:41 GMT

Hi, try this.

Re: Re: Num of colum with and without data

Fri, 11 Apr 2014 07:47:24 GMT

that's a possible solution but, not actually is that i need

Re: Num of colum with and without data

Fri, 11 Apr 2014 09:39:00 GMT

Federico,

i think, you have to add some dummy records into your data. Then you will have for each year/person additional cells in your pivot. Next step will be to have some TypeID. Real data will have 1 for example, First subtotal you nedd -2 ,next 3, etc.

Then you will be possible to write your expression like this:

Pick(TypeID, <expression for your standard KPI>, <Expression for your standard KPI total>, <Expression for your count month number>,<expression for your count number of existing KPI's>)

I think it should work

Let me know if you need more help. If yes - i may try to prepare some example, unfortunately, for about two hours I will be able to take care of it....

regards

Darek

Re: Num of colum with and without data

Roop — Fri, 11 Apr 2014 10:26:31 GMT

The results from the following:

Value is sum(Value)

Month is Count(DISTINCT month)

Filled is Count( if(LEN (Value) > 0, Value))

I hope this helps

Re: Re: Num of colum with and without data

Fri, 11 Apr 2014 10:56:07 GMT

Do you like it?

Re: Num of colum with and without data

Fri, 11 Apr 2014 11:05:37 GMT

IS perfect, but i have a problem .

this table contain more tan 12.millons row and aggr() is not good with this num if record right?

Re: Num of colum with and without data

Fri, 11 Apr 2014 11:20:36 GMT

last time i had 800 mln rows ...

Please check how does it work loaded . I will think a little if there is another possible way. Maybe interrecord functions?

regards

Darek

Re: Num of colum with and without data

Fri, 11 Apr 2014 11:22:42 GMT

LET ME CHECK IT I WILL TELL YOU SOMETHING.

REGARDS

Re: Re: Num of colum with and without data

Fri, 11 Apr 2014 11:28:58 GMT

Here you have without aggr

In previous version i loaded null records. This is why i had aggr.

Re: Num of colum with and without data

Fri, 11 Apr 2014 11:49:38 GMT

THANKS A LOT