topic Re: Duplicate values in bar Chart in QlikView

Duplicate values in bar Chart

Wed, 15 Feb 2017 07:19:41 GMT

Dear experts,

I am trying to create a bar chart with 2 dimensions, however I am ending up with duplicate values,

the expressions are as follows:

Bar chart .. Sum(cash)

line graph .. Sum({$<BDF_Type={"*profit*"}, YEAR = {'2015'} >} cash) / Sum({$<BDF_Type={"*revenue*"}, YEAR = {'2015'} >} cash)

I have attached screen shot for your reference.

Kind regards,

Royce

Re: Duplicate values in bar Chart

Chanty4u — Wed, 15 Feb 2017 07:22:07 GMT

did you tried to check the supress null values in dimension tab?

Re: Duplicate values in bar Chart

sasikanth — Wed, 15 Feb 2017 07:23:09 GMT

HI,

instead you can go for a single dimension MonthYear like 2015-Jan, 2015-Feb etc.

Ex: MonthName(date-Field)

Re: Duplicate values in bar Chart

Wed, 15 Feb 2017 07:23:48 GMT

Yes I have, and it does not seem to work !

Re: Duplicate values in bar Chart

Chanty4u — Wed, 15 Feb 2017 07:24:51 GMT

in screen shot its not there ..same do in presentation tab

Re: Duplicate values in bar Chart

Wed, 15 Feb 2017 07:24:56 GMT

It seems to be showing the value for every month, is there a way to code the expression in a way that it only selects the year and not the month ?

Re: Duplicate values in bar Chart

Wed, 15 Feb 2017 07:28:49 GMT

tried it out and it does not seem to work as well

Re: Duplicate values in bar Chart

Wed, 15 Feb 2017 07:30:17 GMT

I have just changed it, and it still does nothing, the same chart comes up

Re: Duplicate values in bar Chart

sasikanth — Wed, 15 Feb 2017 07:33:40 GMT

try to remove YEAR filter from your second expression. so that the amount will be divided based on specified time dimension values.

Sum({$<BDF_Type={"*profit*"} >} cash) / Sum({$<BDF_Type={"*revenue*"}>} cash)

Re: Duplicate values in bar Chart

Chanty4u — Wed, 15 Feb 2017 07:34:24 GMT

try below expression for bar

=sum(Distinct Cash)

Re: Duplicate values in bar Chart

sdmech81 — Wed, 15 Feb 2017 07:35:17 GMT

Hi,

Restrict ur dimension in Calculated dimension tab.

Sachin

Re: Duplicate values in bar Chart

Wed, 15 Feb 2017 07:36:11 GMT

What if I want only the total fiscal years such as 2015/2016/2017 to be in the selection and not other date values ? I think I tried that option out and it gave me back values for every month as well

Re: Duplicate values in bar Chart

sdmech81 — Wed, 15 Feb 2017 07:37:38 GMT

For Year u just say:

=Only({<Year={'2015'}>}Year)

Sachin

Re: Duplicate values in bar Chart

sasikanth — Wed, 15 Feb 2017 07:39:26 GMT

Please share a sample data set along with the requirement ,it would be easy to resolve .

Re: Duplicate values in bar Chart

sdmech81 — Wed, 15 Feb 2017 07:39:40 GMT

Duplicates u r seeing bcs ur dimensions conatin that data and Qlikview is interpreting it correctly only.

Which all u don't want to see restrict in Calculated dimension bcs u gt 2 dimnesons, like as said above n try:)

sachin

Re: Duplicate values in bar Chart

Wed, 15 Feb 2017 07:41:12 GMT

Sum({$<BDF_Type={"*profit*"}, Only({<Year={'2015'}>}Year) >} cash) / Sum({$<BDF_Type={"*revenue*"}, Only({<Year={'2015'}>}Year) >} cash)

is this what you mean ?

Re: Duplicate values in bar Chart

Wed, 15 Feb 2017 07:47:23 GMT

or is the expression

Sum({$<BDF_Type={"*profit*"}, YEAR =Only({<Year={'2015'}>}Year) >} cash) / Sum({$<BDF_Type={"*revenue*"}, YEAR =Only({<Year={'2015'}>}Year) >} cash)

Re: Duplicate values in bar Chart

Wed, 15 Feb 2017 08:30:11 GMT

heya Chanty do you know where to apply the only() function ? I have tried it inthe syntax and all, it does not work

Re: Duplicate values in bar Chart

Chanty4u — Wed, 15 Feb 2017 09:09:52 GMT

Try this

=Sum({$<BDF_Type={"*profit*"}, YEAR =Only({<Year={'2015'}>}Year) >} cash) / Sum({$<BDF_Type={"*revenue*"}, Only({$<Year={'2015'}>}Year) >} cash)

Re: Duplicate values in bar Chart

Wed, 15 Feb 2017 09:15:50 GMT

thanks for helping out again buddy ! however I am getting an error in set modifier expression showing up