Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Num of colum with and without data

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.

18 Replies
Not applicable
Author

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

Not applicable
Author

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

Roop
Specialist
Specialist

qv_demo.png

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

Not applicable
Author

Do you like it?

Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

LET ME CHECK IT I WILL TELL YOU SOMETHING.

REGARDS

Not applicable
Author

Here you have without aggr

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

Not applicable
Author

THANKS A LOT