Solved: How do I create a chart like this? - Qlik Community

alexpanjhc · ‎2013-09-05

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!

rbecher · ‎2013-09-08

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

Astrato.io Head of R&D

View solution in original post

rustyfishbones · ‎2013-09-05

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

alexpanjhc · ‎2013-09-05

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?

rbecher · ‎2013-09-05

Hi Alex,

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

- Ralf

Astrato.io Head of R&D

rustyfishbones · ‎2013-09-05

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!

rwunderlich · ‎2013-09-05

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

alexpanjhc · ‎2013-09-06

Rob,

Yes, the was the goal.

alexpanjhc · ‎2013-09-06

Ralf,

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

rbecher · ‎2013-09-06

Would be good to have some sample data..

Astrato.io Head of R&D

alexpanjhc · ‎2013-09-06

Maybe this will help a little bit?