topic Re: How do I display last 30 days, but accumulate from entire data set in a chart in QlikView

How do I display last 30 days, but accumulate from entire data set in a chart

Tue, 21 Jul 2015 16:15:46 GMT

I am tasked with creating some metrics based on currently selected data.

Currently I use an "auto-sizing" dimension variable that changes the chart dimension based on how much data is available. I am also (thanks to help from here) now able to accumulate proper numbers over time. I allow the users to select several different fields from list boxes to narrow down what the chart shows.

What I need to do now is shrink the visible window in the chart to the past 30 days, but accumulate over the entire data set so that the last number display is the current total of all issues based on selections. Below is a copy of my chart, and the dimension I currently use.

Based on experimentation, if I use set analysis in my expression and use the 1, it would appear to invalidate selections made by the user. I currently use the $ to do analysis based on current selections.

How do I limit my view to 30 days without impacting my accumulated values?

Expression:

Count(DISTINCT {$<[Target Gate] = {'Alpha - platform'}>} [Master ID])

Dimension:

=if(GetPossibleCount([CreatedDate]) <=36, Date([CreatedDate], 'YYYY-MM-DD'),

if(GetPossibleCount([Created MonthYear]) <= 36, Date([Created MonthYear],'MMM-YY'),

[Year]

)

Re: How do I display last 30 days, but accumulate from entire data set in a chart

sunny_talwar — Tue, 21 Jul 2015 16:23:59 GMT

Are you using full accumulation in your chart? If that's true you won't be able to display partially and yet show full accumulation. For this you will have to use RangeSum(Above()) functions. Do you have a sample data you can share?

Re: How do I display last 30 days, but accumulate from entire data set in a chart

Tue, 21 Jul 2015 16:38:27 GMT

I am using full accumulation. Here is a sample of the data I'm using (I've cut it down from the several thousand items it actually is). The CreatedDate field is a calendar table linked to a master calendar:

Re: How do I display last 30 days, but accumulate from entire data set in a chart

sunny_talwar — Tue, 21 Jul 2015 16:40:01 GMT

Is it possible to give this data in a excel file? I would have to type that information in manually otherwise.

Thanks,

Sunny

Re: How do I display last 30 days, but accumulate from entire data set in a chart

Tue, 21 Jul 2015 16:42:26 GMT

Here is the sample data in Excel format.

Re: How do I display last 30 days, but accumulate from entire data set in a chart

sunny_talwar — Tue, 21 Jul 2015 17:28:58 GMT

See if this helps?

Re: How do I display last 30 days, but accumulate from entire data set in a chart

Tue, 21 Jul 2015 17:53:52 GMT

My Qlikview program is telling me if I open that file, I won't be able to open any other files created with my current key.

Any way to post the Dimension and Expression used?

It looks like what I need.

Re: How do I display last 30 days, but accumulate from entire data set in a chart

sunny_talwar — Tue, 21 Jul 2015 17:57:55 GMT

Script:

Table:

LOAD *,

Date(Floor(CreatedDate)) as Date,

MonthName(CreatedDate) as MonthYear;

LOAD [Master ID],

CreatedDate,

[Issue Status]

FROM

Community_173461.xlsx

(ooxml, embedded labels, table is Sheet1);

Dimension:

MonthYear

Expression:

If(Count(DISTINCT {<Date = {"$(='>=' & Date(AddMonths(MonthStart(Max(Date)), -1)) & '<=' & Date(MonthEnd(Max(Date))))"}, MonthYear = >}[Master ID]) > 0,

RangeSum(Above(Count(DISTINCT {<MonthYear = >} [Master ID]), 0, RowNo())))

Re: How do I display last 30 days, but accumulate from entire data set in a chart

Tue, 21 Jul 2015 19:03:41 GMT

Ok, took me a little while to parse what you were doing (date math in Set Analysis still messes with my head sometimes).

I have it working, and it gives me two months.

However I'm trying to understand the set analysis you've got in there as I'm sure it will come in handy in other places.

I am assuming by specifying no modifier the set analysis takes places on the currently selected data?

I understand the Date= at the start of the set analysis. Everything after that I'm lost. It looks like a Set Analysis inside a set analysis?

I also don't understand the MonthYear = at the end. Is that stating that as long as there's a value for MonthYear, make the comparison?

I'm asking because I'm going to convert this expression so I chart MonthDay, instead of MonthYear.

Re: How do I display last 30 days, but accumulate from entire data set in a chart

sunny_talwar — Tue, 21 Jul 2015 19:09:44 GMT

Best way to understand the date math is to put the middle part in a test box object to see what it evaluates to. This

='>=' & Date(AddMonths(MonthStart(Max(Date)), -1)) & '<=' & Date(MonthEnd(Max(Date)))

So I am telling the expression to only select dates between this range and MonthYear = means that disregard any selections made in MonthYear field because I am controlling MonthYear indirectly through Date field.

Does that clear some things?

Re: How do I display last 30 days, but accumulate from entire data set in a chart

Tue, 21 Jul 2015 19:30:09 GMT

That definitely cleared quite a few things up. I never thought to use a text object to vet expressions (was actually looking for a way to do that).

Quite a handy tip.

Re: How do I display last 30 days, but accumulate from entire data set in a chart

sunny_talwar — Tue, 21 Jul 2015 19:40:04 GMT

Glad you were able to learn a new trick here today

Best,

Sunny

Re: How do I display last 30 days, but accumulate from entire data set in a chart

Kaushik2020 — Tue, 07 Nov 2023 10:43:09 GMT

If(Count(DISTINCT {<Date = {"$(='>=' & Date(AddMonths(MonthStart(Max(Date)), -1)) & '<=' & Date(MonthEnd(Max(Date))))"}, MonthYear = >}[Master ID]) > 0,

RangeSum(Above(Count(DISTINCT {<MonthYear = >} [Master ID]), 0, RowNo())))

this expression actually helped me for one of the instances. thanks a lot