Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
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[:)]

17 Replies
dennisnet
Partner
Partner

Take a look at what I add, maybe this is a solution for you:

Not applicable
Author

Hi Dennis,

I got what you mean now. Thanks for your sample.

Btw, by using your method, the output is slightly different from mine because the break down of the latest month is missed. Any idea to achieve this? I tried several ways but no luck at all.

Thanks

dennisnet
Partner
Partner

Sorry made a paste and copy mistake in the expression Smile

It should be:

count (distinct readcontactid) + count (distinct createcontactid)

(not : count (distinct readcontactid) + count (distinct readcontactid) ) Smile

Not applicable
Author

Hi Dennis,

Yes, I noticed that and I had made the change already. Wink

But in my previous post, as I mentioned, by puting the YrMth as the another dimension on top, the break down for the LATEST YrMth (I just want to show the break down for LATEST MONTH ONLY) is missed. Can this be achieved?

Thanks

dennisnet
Partner
Partner

Sorry I don't get it.
What do you mean by a break down?

Not applicable
Author

Hi Dennis,

I attached the excel screen shot in my question. There are ReadCount and CreateCount field just next to UserName. These 2 fields are the break down for the Jan11 total.

ReadCount + CreateCount = Jan11

Thanks

dennisnet
Partner
Partner

Is this what you are looking for:



Count({$<YrMth={$(=Max(YrMth))}>} DISTINCT readcontactid)
+
Count({$<YrMth={$(=Max(YrMth))}>} DISTINCT createcontactid)


Not applicable
Author

Hi Dennis,

Thanks again but this is not what I want.

The requirements for a single pivot table are:

  1. Display the past 12 months figures. Now is Feb2011, so need to display Feb2010 to Jan2011 total figures.
  2. For Jan2011, please break down the total into pieces. I.E., regarding to my example, need to display the ReadCount and CreateCount in seperate field for JAN2011 ONLY.

Hope this clear.

Thanks