topic Re: MTD DATA in QlikView

MTD DATA

mandilicious — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

Please assist i need to get MTD information using below and attached data.

Where:

Detractor - Scores between 0 and 6

Promotor - Scores between 9 and 10

Neutral - Scores 7 and 8

I need to know Detractor MTD Scores,Promotor MTD and Neutral MTD Scores.

Thanks

Re: MTD DATA

zhadrakas — Thu, 21 Dec 2017 12:02:02 GMT

is that what you are looking for?

LOAD DATE,
LEAD_ID,
ANSWER1,
ANSWER2,
ANSWER3,
SCORES,
if(SCORES >=0 AND SCORES<=6, SCORES) as [Detractor MTD],
if(SCORES >=7 AND SCORES<=8, SCORES) as [Neutral MTD],
if(SCORES >=9 AND SCORES<=10, SCORES) as [Promotor MTD],
AGENT,
PHONENUMEBR
FROM INFO.xlsx (ooxml, embedded labels, table is Sheet1);

Re: MTD DATA

mandilicious — Thu, 21 Dec 2017 12:21:15 GMT

Hi Tim

Thanks if i may ask what if i need totals for current month?let me say i also have data for 17/12/01(2017-12-01) - Date and i need to get data for current month without any selection?

Thanks

Re: MTD DATA

zhadrakas — Thu, 21 Dec 2017 12:26:59 GMT

Change your script like this

LOAD DATE,

month(DATE) as MONTH,

year(DATE) as YEAR,
LEAD_ID,
ANSWER1,
ANSWER2,
ANSWER3,
SCORES,
if(SCORES >=0 AND SCORES<=6, SCORES) as [Detractor MTD],
if(SCORES >=7 AND SCORES<=8, SCORES) as [Neutral MTD],
if(SCORES >=9 AND SCORES<=10, SCORES) as [Promotor MTD],
AGENT,
PHONENUMEBR
FROM INFO.xlsx (ooxml, embedded labels, table is Sheet1);

Now you can create a diagram with Dimension year and Month.

Your Expression is: sum(Scores)

Re: MTD DATA

mdmukramali — Thu, 21 Dec 2017 12:35:04 GMT

Hi,

PFA.

Re: MTD DATA

mandilicious — Thu, 21 Dec 2017 12:43:43 GMT

Hi Mohammend

if i may ask is it a way that i can see totals for Promoter, Neutral MTD without selecting a month or Date? Just after refreshed my dashboard i don't need to select anything? but get Promoter, Neutral MTD .

Re: MTD DATA

mandilicious — Thu, 21 Dec 2017 12:46:15 GMT

Hi Tim

i need to see totals for Promoter, Neutral MTD without selecting a month or Date? cause everything need to be displayed without any selection?

Re: MTD DATA

zhadrakas — Thu, 21 Dec 2017 13:00:33 GMT

ok thats different. then i would use this script

LOAD DATE,

month(DATE) as MONTH,

year(DATE) as YEAR,
LEAD_ID,
ANSWER1,
ANSWER2,
ANSWER3,
SCORES,
if(SCORES >=0 AND SCORES<=6, [Detractor],
if(SCORES >=7 AND SCORES<=8, [Neutral],
if(SCORES >=9 AND SCORES<=10, [Promotor]))) as MTD_TYPE,
AGENT,
PHONENUMEBR
FROM INFO.xlsx (ooxml, embedded labels, table is Sheet1);

Create a diagram with dimensions Year, Month, MTD_Type

Expression is sum(SCORES)

Re: MTD DATA

mdmukramali — Thu, 21 Dec 2017 13:39:28 GMT

Hi Manti,

i forgot to include Year and Month Fields in the set analysis.

for Detractors MTD:

=SUM({<Result={'Detractor'},Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'}>}SCORES)

for Neutral MTD:

=SUM({<Result={'Neutral'},

Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'}>}SCORES)

for Promotor MTD:

=sUM({<Result={'Promotor'},

Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'}>}SCORES)

kindly find the attached updated File.

Thanks,

Mukram

Re: MTD DATA

mandilicious — Thu, 21 Dec 2017 14:56:28 GMT

Hi Mohammend

Thank you very much for your help it is working but i have to click month to get totals for Promoter, Neutral etc.

maybe i am missing a point. They don't want to click to get totals.

Re: MTD DATA

mandilicious — Thu, 21 Dec 2017 15:02:36 GMT

Hi Tim

I really appreciate your help, i did as you said but cause i have many months in my side i have to drill per month to get MTD value, if i can just refresh and get MTD value without clicking any value(Month e.g Dec ) in my Month table.

Thanks

Re: MTD DATA

MK9885 — Thu, 21 Dec 2017 19:32:53 GMT

Remove Year from dimension and just use Month and MTD Value?

This would break the bar chart into different months.... if you don;t want to click on month.

Re: MTD DATA

mandilicious — Thu, 21 Dec 2017 22:33:20 GMT

Thanks a lot Tim

Re: MTD DATA

mandilicious — Thu, 21 Dec 2017 22:34:30 GMT

Thank you very much it works

Re: MTD DATA

mandilicious — Thu, 18 Jan 2018 10:55:09 GMT

Hi All

Quick one i know its being long and answered what you suggested it is working when i use bar chart. But client just need text box not bar chart.i am using below formula(=count({<Result={'Detractor'},Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'}>}ANS4) )in my textbox but have to click per month to get results.

Please help.

Re: MTD DATA

mandilicious — Thu, 18 Jan 2018 10:55:58 GMT

Hi All

Please help.

Re: MTD DATA

mdmukramali — Thu, 18 Jan 2018 11:29:52 GMT

Hi Phillip,

=count({<Result={'Detractor'},Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'}>}ANS4)

when you use this expression you don't need to select any month from Month Field default it will take the Year and Month from Max Date.

Maybe check what is the Max Date for your application and check the ANS4 values for that Date.