Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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:

error loading image

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[Smiley Happy]

1 Solution

Accepted Solutions
Not applicable

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

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.

17 Replies
Not applicable

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

Anyone can help me?

Thanks

dennisnet
Not applicable

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

Hi Welcom to Qlikview.

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

Not applicable

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

[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

dennisnet
Not applicable

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

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"

dennisnet
Not applicable

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

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

Not applicable

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

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.

Not applicable

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

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

Not applicable

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

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.

Not applicable

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

Shans_bala, thanks a lot.