Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a bar chart cycle drill (week and month) which highlights some KPI From 2015 to the Current Year.
Each year I have a monthly and a weekly target for that KPI, which is for example:
2015: 200 monthly and 50 weekly
2016: 175 monthly and x weekly
2017: 175 monthly and so on
2018: 200 ....
SO i would like my target to change based on my dates week and month.
The thing is i tried to put in excel but it doesn't work because i have a master calendar.
DO you have any solution ? like a specific formula to apply ?
The goal is to let me or one of the key user just write down the kpi number in a variable.
So the calculation could be done automatically.
But if it's not possible how do I do for the values in the excel i posted.
Because Qlikview won't recognized a field with only MM (example: January --> KPI = 200).
Do you have a solution ?
Here are my sample files attached.
Thank you
Yes It's normal.
Because It's the current month.
Every week i have to display various kind of KPI in a meeting for my service.
So My datas are basically
Date: 05/05/18 Ticket Number: xxx5gg KPI: 134 ISSUE: text / Assign TO: xxxx
So that's why i use a master calendar.
Thank you for the tip.
But unfortunately I have another issue
It appears that way because i made the change in the master calendar repalcing this : (
min(Created) as minDate,
max(Created) as maxDate
Resident MyTable1; ) by this
Temp:
Load
YEARSTART(min(Created)) as minDate,
YEAREND(max(Created)) as maxDate
Resident MyTable1;
If you don't have the answer it's ok you helped me a lot.
Maybe there isn't a solution i don't know..
Hi Yann,
Sorry for the late reaction. YEAREND is 31-12-2018 23:59:59 which isn't a whole number. You can use ROUND( YEAREND(max(Created)),1) or DAYSTART( YEAREND(max(Created))), or FLOOR( YEAREND(max(Created))) to make it an integer instead of a decimal number.
Should be the last piece of the puzzle 
It's working thank you !
But I have one last question (it really is my last one haha)
.PNG)
The script you gave me help to display the right target.
Although in my bar chart, it displays the targets until the end of the year 2018. ANd currently we are in june so there is no need to display the following target value if there is no data to compare with.
Is there a way to display only the target when there is data value ?
Thanks a lot anyway
I made a huge step forward thanks to you
Thanks for the response.
I tried what you recommanded me.
But I get an error message unfortunately
When I load script, Qlikview can't located the created field
HI,
I still have an error message
And Here is my script
// NoOfDays
temp01_Target:
NOCONCATENATE LOAD
Year,
COUNT(Created) AS [NoOfDays]
RESIDENT MasterCalendar
GROUP BY Year;
// target figures
LEFT JOIN (temp01_Target) LOAD
Year,
KPI,
Target*12 AS Target
INLINE [
Year, Target, KPI
2018, 120, LBTARGET
2018, 25, LETARGET
2018, 35, MTTRTARGET
2017, 300, LBTARGET
2017, 30, LETARGET
2017, 40, MTTRTARGET
2016, 600, LBTARGET
2016, 60, LETARGET
2016, 50, MTTRTARGET
2015, 300, LBTARGET
2015, 30, LETARGET
2015, 40, MTTRTARGET
];
Target:
NOCONCATENATE LOAD
Year,
Target/[NoOfDays] AS Target,
KPI
RESIDENT temp01_Target;
DROP TABLE temp01_Target;
LEFT JOIN (Target) LOAD
Year,
Created
RESIDENT MasterCalendar;
DROP FIELD Year FROM Target;
FinalTarget:
NOCONCATENATE LOAD
Created,
KPI,
Target
RESIDENT Target
WHERE DAYSTART(MONTHEND(Created)) <= DAYSTART(MONTHEND(TODAY()));
DROP TABLE Target;
