topic Re: Chart with unique values on Dimension in QlikView

Chart with unique values on Dimension

Tue, 15 Mar 2016 07:13:14 GMT

Hello,

I have a line chart with Date as dimension and count Email address as expression.

The problem is that the chart shows all dates, instead of aggregating per date.

How can I show only unique values as dimension?

Re: Chart with unique values on Dimension

Chanty4u — Tue, 15 Mar 2016 08:47:14 GMT

hi,

Count or Count distinct?

= count(DISTINCT aggr(count( email),submtdate))

count( {<Column name={'Name'}>}Distinct Documnent2)

Re: calculation distinct

Re: Chart with unique values on Dimension

sunny_talwar — Tue, 15 Mar 2016 08:56:50 GMT

I think your date field might look like a date field, but may contain time information making it to repeat in the chart. I suggest fixing this within your script. You can do something like this:

LOAD SubmitDate as SubmitTimeStamp,

Date(Floor(SubmitDate)) as SubmidDate

FROM Source

Re: Chart with unique values on Dimension

amayuresh — Tue, 15 Mar 2016 10:05:15 GMT

Please post your sample Data.

Do you have time in Date field ?

Re: Chart with unique values on Dimension

Anonymous — Tue, 15 Mar 2016 10:31:30 GMT

may be this?

In Dimension Tab - > Update your Date Dimension with this -> Date(DateField)

Re: Chart with unique values on Dimension

sunny_talwar — Tue, 15 Mar 2016 10:33:57 GMT

Balraj that won't change a thing. You can do this instead -> Date(Floor(DateField)) to get rid of time portion

Re: Chart with unique values on Dimension

Anonymous — Tue, 15 Mar 2016 10:51:47 GMT

Is floor() really required??

In some cases I extract date from Date/Time field using same

Re: Chart with unique values on Dimension

sunny_talwar — Tue, 15 Mar 2016 11:00:25 GMT

If you have a timestamp field and the goal is to extract just the date portion, I am almost certain that you cannot do it without using Floor function. Because without floor the date will look like a date, but it will still contain the decimals representing time portion.

Re: Chart with unique values on Dimension

Kushal_Chawda — Tue, 15 Mar 2016 11:05:21 GMT

try to do it in script like below

EmailCount:

load distinct SubmitDate,

SubmitDate as SubmitDateNew

Count(distinct Email) as EmailCount

resident your Data

group by SubmitDate;

Now Create the Chart,

Dimension:

SubmitDateNew

Expression:

Sum(EmailCount)

Re: Chart with unique values on Dimension

Digvijay_Singh — Tue, 15 Mar 2016 11:55:55 GMT

Good one

Re: Chart with unique values on Dimension

Anonymous — Tue, 15 Mar 2016 12:03:51 GMT

The DayName() and DayStart() functions also remove / zero the time decimals.

But I still prefer using the Floor() function as it is far easier to see what is happening in the script from a quick eyeball.

Re: Chart with unique values on Dimension

brijesh1991 — Tue, 15 Mar 2016 12:08:28 GMT

Add a new field in your script:

Load *,

Date(MakeDate(Year(SubmitDate),Month(SubmitDate),Day(SubmitDate)),'DD/MM/YYYY') as SubmitDateNew

From Table

Use this new field in your chart dimention.
Expression: Count(Distinct Email)

Re: Chart with unique values on Dimension

Tue, 15 Mar 2016 16:12:17 GMT

Hi Sunny T. This solution fixed the problem. Thanks a lot and have a nice day.