Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
lessassy
New Contributor III

Use a combo chart to display a dynamic target

Hello everyone,

I have a bar chart which represent on x axis some dates and on y axis some values

Year to date Histogramme.PNG

I have a target for each month.

SO i said using a combo chart could be a solution to resolve the problem.

But as I'm new and not very skilled enough with formula I don't really know how to process.

Has anyone had this issue before ?

Thank you ?

10 Replies
Partner
Partner

Re: Use a combo chart to display a dynamic target

You can add the second measure (Target in your example) and tick the check box as Line in display options under Expressions.

Note: You have to use combo chart.

Combochart.PNG

Thanks,

Shan S

lessassy
New Contributor III

Re: Use a combo chart to display a dynamic target

Yeah thank you.

TO do that i know.

But what i want to do is to display a target for each year (2015 to 2018).

SO do i need to create 4 distinct line chart.

ANd if created what is the exact formula to put ?

Thank you

Partner
Partner

Re: Use a combo chart to display a dynamic target

don't need to write 4 expressions, if you have a measure called target and which is having value for all years (2015 to 2018), then the line chart will display continuously.

If not clear please upload a sample app, I will try and let you know.

Thanks,

Shan S

lessassy
New Contributor III

Re: Use a combo chart to display a dynamic target

The thing is I don't have a measure of the goal on my excel files.

It's not located on any field.

I know that the target are 100 150 200 175. And I wanted to include this number in a formula.

With the form: "If -->date =2015, then 100 etc"

Is it possible ?

lessassy
New Contributor III

Re: Use a combo chart to display a dynamic target

Sample Loss BU.PNGLossBU Month.PNGMenu target.PNGDynamic Target Line.PNG

Target target.PNG

I don't know how to upload an excel and a qvw file. But anyway here are all my datas.

Basically my target are monthly but In the field created i put the date in this format so my master calendar could read it. "

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

 

Temp: 

Load 

               min(Created) as minDate, 

               max(Created) as maxDate 

Resident MyTable1; 

 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

 

MasterCalendar: 

Load 

               TempDate as Created, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;  "

I have a cycle drip per chart which contain week and month. And i want to display my monthly target. And i want also my target to be converted in week for the weekly x axis

Have you got enough informations ?

lessassy
New Contributor III

Re: Use a combo chart to display a dynamic target

Here are simplified version of my files.

By the way i'm using qlikview personal edition so I won't be able to modify the file...

Partner
Partner

Re: Use a combo chart to display a dynamic target

Hi,

While generating master calendar you're using min(created) & max(created) from the Mytable1 so master calendar is ignore 1/1/2015 which is not present in the Mytable1 and this is the reason the year/month/week-year is not having any values in the chart and line looks weird.

sample.png

Share the excel files you're using so that I can try to help you.

Thanks,

Shan S

lessassy
New Contributor III

Re: Use a combo chart to display a dynamic target

There is supposed to be an other excel file.

But it's not useful for this particular case.

Anyway thank you a lot

Partner
Partner

Re: Use a combo chart to display a dynamic target

can you upload the Sample.XLSX you have used in the sample application.

Thanks,

Shan S