How to display Previous Month total count with latest month count figures

[#1]

Hi all,

I am new to the QlikView and I am trying to develop a report that shows the past month's usage count while needs to display the year to month count as well. The sample tables structure are as below

• User table (userid + year_month as KEY1, userid + year_month as KEY2, username, userid, team, year_month)
• ReadAction table(userid + year_month(read_date) as KEY1, userid, readactionid, read_info, read_date)
• CreateContact table(userid + year_month(create_date) as KEY 2, userid, createactionid, createcontact_info, create_date)

Output would be something like the following:

I try to accomplish this with IF statement but the performance is very very slow.

Additional info

• ReadCount is the number of read count in Jan2011
• CreateCount is the number of create count in Jan2011.
• Jan11 = ReadCount + CreateCount

Any other formula can achieve this?

Thanks[:)]

[#2]

Anyone can help me?

Thanks

[Anonymous]

Hi Welcom to Qlikview.

Can you upload your file? Then I will have a look.

[#4]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/7206.QVsample.zip:550:0]

Hi Dennis, thanks for your reply. the sample file is uploaded as zip.

btw, I would also like to know how can I format the sub-total line, like color it, bold the text, etc?

Many thanks

[Anonymous]

I see why it is getting slow. You might want to try and do some calculation already in the loading script, that will be faster.

An other nice trick you can use in Qlikview (which will make your document easier to read) is that you can just use the names of the coloms in your expressions, like:

([CreateContact Count])+([Read Count])

or

(column(1) + column(2))


For subtotals you go to the presentation tab select your dimension (User name) and check "Show Partial Sum"

[Anonymous]

Why don't you use your YrMth as a dimension? That would make more sence to me.

[#7]

Hi Dennis,

Thanks for information. In fact, I think of this way of doing but this is not the way I want to do because this qvw file will consists of lots of different types of reports from different point of view. But of course, if this is the only way of doing it, i have no choice and have to follow.

For the subtotal issue, I know we can go to "presentation" tab and check the "show partial sum", but what I want is, for example, high-light the sub-total line in RED and bolded.

Thanks.

[#8]

Hi Dennis,

Re your question, I was think of make the YrMth as a dimension, but in such case, the data model will have a loop??

Btw, I want to keep the data level as low as possible and so, I can perform another kind of analysis with the same data. In fact, I am more familiar with Cognos and this is what I did in Cognos. Wondering if QV can achieve this in the same way.

Thanks

[#9 · Accepted solution]

Hi,

To highlight the Sub total line, Go to View - Design Grid and now you can see the "Custom Format Cell" option in the floating menu of that table. Using that you can change the format of the sub total line.

[#10]

Shans_bala, thanks a lot.