Solved: Adding data of prev. years or aggregating prev. ye... - Qlik Community

Report Inappropriate Content · ‎2012-02-28

Hi,

Not sure the subject is relevant. I want to achieve following results:

I have data sampled from past year till this year. But when displaying the data either in a graph or a pivot ,I am displaying only current year and months. For which the appropriate data is getting displayed. What I want is the prev. years data should be aggregated i.e added up into this data and displayed.

In the attached ex. there is a policy count of 117 till dec. 2011. And for month of Jan 2012 the count is 22, for feb 2012 it is 58 and so on...

When the graph is displayed for Jan 2012,Feb 2012 on x axis and policy count on y axis. I could see 22 and 58, there is a 219 policy count shown under no date i.e blank. When I suppress the blanks this count ( 219) disappears. This count of 219 should be added up in the count of jan 2012. as illustrated below.

In the graph I should see for Jan 2012 the count of (219+22=241) for feb 2012 ( 241+58=299) etc.

In the pivot I should see for Jan 2012 and for the dimension (keyagebucket) ( 13+5 = 18) for feb 2012 ( 18+4=22) etc

Please help how can this be achieved.

Thanks

Lax

CELAMBARASAN · ‎2012-03-05

Hi,

Ya you can do that.

Use QuaterStart for Deciding how many quaters you want.

Example:For last 4 quarters

=Sum({<EXTRACTIONDATE={">=$(=TimeStamp(QuarterStart(Max(EXTRACTIONDATE),-4)))"}>}MTD_COUNT_SURRENDERABLE)+Alt(Above(TOTAL [Surr1 policy count]),$(=Sum({1<EXTRACTIONDATE={"<$(=TimeStamp(QuarterStart(Max(EXTRACTIONDATE),-4)))"}>} MTD_COUNT_SURRENDERABLE)))

Use MonthStart for deciding how many months you want.

Example:for last 6 months

=Sum({<EXTRACTIONDATE={">=$(=TimeStamp(MonthStart(Max(EXTRACTIONDATE),-6)))"}>}MTD_COUNT_SURRENDERABLE)+Alt(Above(TOTAL [Surr1 policy count]),$(=Sum({1<EXTRACTIONDATE={"<$(=TimeStamp(

MonthStart

(Max(EXTRACTIONDATE),-6)))"}>} MTD_COUNT_SURRENDERABLE)))

Hope it helps

Celambarasan

View solution in original post

Report Inappropriate Content · ‎2012-03-01

Hi,

Am I not clear with the problem ? Or is it complex to get the solution? Can some one pls download the doc. and try what I want to achieve.

CELAMBARASAN · ‎2012-03-01

Hi,

I think your requirement is same as this discussion.

Check this link http://community.qlik.com/message/195158#195158

If not let me know.

Celambarasan

Report Inappropriate Content · ‎2012-03-01

Report Inappropriate Content · ‎2012-03-01

In the above chart I want only the highlighted data to be displayed. The data is cumulative.

Dimension is Date(Extractiondate,'DD-MM-YYYY') and expression is sum(pcount)

Thanks,

Lax

Report Inappropriate Content · ‎2012-03-02

Hi, My need is somewhat similar. Though I have the past data I don't want to display that data for past years but instead be shown as accumulated for the current months of the current year. Same thing for pivot.

What I have done is that I am displaying current year's month using if condition in the dimension.

CELAMBARASAN · ‎2012-03-02

Hi,

What expression are you using now?

Celambarasan

Report Inappropriate Content · ‎2012-03-02

I am using sum(MTD_COUNT_SURRENDERABLE).

I tried using

aggr(sum(MTD_COUNT_SURRENDERABLE),EXTRACTIONDATE) but gave me same results.

Lax

CELAMBARASAN · ‎2012-03-02

Hi,

I have asked you about whole expression?

Ok.Have you tried like this?

=Sum(MTD_COUNT_SURRENDERABLE)+Alt(Above(TOTAL [Accumulate]),$(=Sum({1<DateField={"<$(=Min(DateField))"}>} MTD_COUNT_SURRENDERABLE)))

Celambarasan

Report Inappropriate Content · ‎2012-03-02

Hi,

For pivot dimension is

=if(YEAR(EXTRACTIONDATE)>'2011',DATE(EXTRACTIONDATE,'DD-MM-YYYY'))

and the new expression as per your suggestion is [Surr1 policy count]

=Sum(MTD_COUNT_SURRENDERABLE)+Alt(Above(TOTAL [Sur Policy Count]),$(=Sum({1<EXTRACTIONDATE={"<$(=Min(EXTRACTIONDATE))"}>} MTD_COUNT_SURRENDERABLE)))

Original expression is [Surr1 policy count]

=Sum(MTD_COUNT_SURRENDERABLE)

Similary graph also as the same dimension and expression as sum(mtd_count_surrenderable).

Adding data of prev. years or aggregating prev. years data into current year