topic Re: How do I create a chart like this? in QlikView

How do I create a chart like this?

alexpanjhc — Thu, 05 Sep 2013 19:38:16 GMT

I am looking to create a chart like this:

Total Number	Jan 2012	Feb 2012
Gold	10000	8000

In Feb 2012, I need to know how many of the gold accounts are also gold account in Jan 2012. In March I need to know the same for Feb. so on and so forth until December

I created a vartiable called vPriorMonth, I do not know how to use it in a set analysis.

Any help is appreciated!

Re: How do I create a chart like this?

rustyfishbones — Thu, 05 Sep 2013 20:34:03 GMT

you would need to create 3 variables called vMaxYear, vMaxMonth and vPriorMonth

vMaxYear = MAX(Year)

vMaxMonth = MONTH(MAX(Date))

vPriorMonth = MONTH(ADDMONTHS(MAX(Date) ,- 1))

and you can write a couple of Expressions using those variables

SUM({<Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'}>}GoldAmount)

SUM({<Year={'$(vMaxYear)'},Month={'$(vPriorMonth)'}>}GoldAmount)

Maybe that could help

Re: How do I create a chart like this?

alexpanjhc — Thu, 05 Sep 2013 20:57:07 GMT

I am not sure how this can help

SUM({<Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'}>}GoldAmount) //this is for the most current month

SUM({<Year={'$(vMaxYear)'},Month={'$(vPriorMonth)'}>}GoldAmount) // this is for the prior month

how I can achive the goal with these formulas?

Re: How do I create a chart like this?

rbecher — Thu, 05 Sep 2013 21:04:34 GMT

Hi Alex,

you should consider to use Above() chart inter record function to get the value of the previous month.

- Ralf

Re: How do I create a chart like this?

rustyfishbones — Thu, 05 Sep 2013 21:06:54 GMT

You can create 2 list boxes, one for Year and one for Month

and when you select the a Year it becomes the max year and when you select a month from the Month listbox you will have your max month

try it!

Re: How do I create a chart like this?

rwunderlich — Fri, 06 Sep 2013 00:12:38 GMT

Let me clarify the goal. You do not want the difference in total count per month. You want to know how may specific accounts were gold in the last month and are still gold in the current month. For example, if account "A" was gold in both Jan and Feb, that account will be counted. Sort of a "returning customers" thing. A I correct?

-Rob

Re: How do I create a chart like this?

alexpanjhc — Fri, 06 Sep 2013 13:19:53 GMT

Rob,

Yes, the was the goal.

Re: How do I create a chart like this?

alexpanjhc — Fri, 06 Sep 2013 14:28:14 GMT

Ralf,

I do not think that was what I wanted. I need to get the retention number for the next month.

Re: How do I create a chart like this?

rbecher — Fri, 06 Sep 2013 17:41:22 GMT

Would be good to have some sample data..

Re: Re: How do I create a chart like this?

alexpanjhc — Fri, 06 Sep 2013 18:52:30 GMT

Maybe this will help a little bit?

Re: How do I create a chart like this?

Fri, 06 Sep 2013 18:58:37 GMT

I guess we can handle this in script too. We can make a make a composite key like 'Month_Number-Account'.

For Example:

Month Account Composite Key

Jan 123 1-123

Feb 123 2-123

then calulate Account_Flag (IF left(Composite Key-1)&'-'Account) is present than 'Yes' else 'No'

Hope this works

Re: How do I create a chart like this?

alexpanjhc — Fri, 06 Sep 2013 19:11:53 GMT

Good suggestion for only 2 months, but when it comes to many months, then we do not know which is which month. Right?

so let's say when it comes to March and account 123 is still active, do we marked it yes. But we do not really know if this yes is to what month.

Also how do you know if the composite key present?

Re: Re: How do I create a chart like this?

Fri, 06 Sep 2013 20:00:22 GMT

composite key we need to generate for each month which ia a combination of Month and Account.

If you have data for sevral years you can also add year into composite key

Please find attached application.

Re: How do I create a chart like this?

alexpanjhc — Fri, 06 Sep 2013 20:18:17 GMT

there must be something I am missing. I know your used (IF left(Composite Key-1)&'-'Account)

to create the Prev_month_com_key but how did you know that key must exist or not?

Can you elabrate your idea?

Re: How do I create a chart like this?

Fri, 06 Sep 2013 20:29:07 GMT

If previous month key is not present then it will not load those accounts and months.

In youe requirement we just need to concern with accounts which are present in last month.

Re: How do I create a chart like this?

alexpanjhc — Fri, 06 Sep 2013 20:52:18 GMT

if I know the previous key is or is not present, I would not have problems...

Re: How do I create a chart like this?

rbecher — Fri, 06 Sep 2013 21:34:30 GMT

If you would load the data ordered you can use previous() function in load script to discover retension and set a counter flag on the record you sum up in the chart.

Re: How do I create a chart like this?

rbecher — Sun, 08 Sep 2013 18:16:55 GMT

To give you an idea how to discover retention with previous() function:

Input:
LOAD ID, 
     Period
FROM
D:\Book2.xlsx
(ooxml, embedded labels, table is Sheet1);

Result:
NoConcatenate LOAD ID, 
     Period,
     if(ID=previous(ID) and Period=previous(Period)+1, 1, 0) as Retention
Resident Input
Order By ID, Period;

Drop Table Input;

- Ralf