topic Re: Graph help - MTD vs. YTD in QlikView

Graph help - MTD vs. YTD

Anonymous — Tue, 07 Apr 2015 19:15:08 GMT

Hello,

I have tried to read multiple discussion threads and I cannot seem to convert the information into what is needed. I am looking to create a graph that shows 3 years of monthly trends, and the current year's YTD trend. So far I have only been able to create the monthly trends. Here is what I am using so far

Dimensions - Month and then Year

Expressions - Sum(2015 losses)/sum(2015 premiums)

Sum(2014 losses)/sum(2014 premiums)

Sum(Plan Losses)/sum(plan premiums)

What I am trying to use for YTD incorrectly -

=(Sum ({<[MONTH-NUMERIC] = {"<=$(=max({<YEAR={$(=max(YEAR))}>} [MONTH-NUMERIC]))"}, YEAR={'2015'}>}losses)/

Sum ({< [MONTH-NUMERIC] = {"<=$(=max({<YEAR={$(=max(YEAR))}>} [MONTH-NUMERIC]))"}, YEAR={'2015'}>}[premium))

At this point my data is in Month, Year, and I have a date field I am not using currently of YYYY/MMM. I have read in several articles about a master calendar but I am not sure how to implement with just using month and year. Looking for some help in the right direction to complete the graph properly. Shown below is the graph so far, with both YTD 2015 and MTD 2015 on top of each other.

Re: Graph help - MTD vs. YTD

ramoncova06 — Tue, 07 Apr 2015 19:51:34 GMT

use this for you expression

=(Sum ({<[MONTH-NUMERIC] = {"<=$(=max([MONTH-NUMERIC] ))"}, YEAR={'2015'}>}losses)

I am assuming you were trying to use the max month numeric, right ?

Re: Graph help - MTD vs. YTD

Anonymous — Tue, 07 Apr 2015 19:54:14 GMT

Ramon,

Thank you for the help. I was looking to plot year to date for each month. So for Jan it would only be the Jan numbers for the YTD. For Feb it would be a blend of both Feb and Jan. Will this new set analysis achieve this?

Re: Graph help - MTD vs. YTD

ramoncova06 — Tue, 07 Apr 2015 20:00:48 GMT

you can use full accumulation under expression

Re: Graph help - MTD vs. YTD

Anonymous — Tue, 07 Apr 2015 20:07:30 GMT

Ramon,

When I do the full accumulation the expression does not behave normally. The results should be between 70% and 60% and with full accumulation for the YTD it goes into the 130% range with the following picture:

Re: Graph help - MTD vs. YTD

ramoncova06 — Tue, 07 Apr 2015 20:12:13 GMT

can you share some of the data

Re: Graph help - MTD vs. YTD

Anonymous — Tue, 07 Apr 2015 20:34:19 GMT

Ramon,

Attached is my QVW file. Month two for the year to date should come out to 64.2%.

Thank you for the help,

Justin

Re: Graph help - MTD vs. YTD

Anonymous — Tue, 07 Apr 2015 21:39:10 GMT

Maybe another way to do is create another field i.e. "as of" month. See the attached file. Not a complete solution, just another approach.

Thanks,

Re: Graph help - MTD vs. YTD

Anonymous — Wed, 08 Apr 2015 13:03:49 GMT

Amarbogam,

Thank you for the help. On the update, how would I add in the YTD field to the first graph? Also, I would like to only show through Feb on the YTD graph. When I just try to add it to the first graph, it does show properly. It shows as follows:

I would like to have the bar graph below only through February, added to the top graph if possible.

Thank you,

Justin

Re: Graph help - MTD vs. YTD

Anonymous — Wed, 08 Apr 2015 18:58:20 GMT

Check the attached file.I had to modify your expressions though. Let me know if you have any questions.

Thanks,

Amar.

Re: Graph help - MTD vs. YTD

Anonymous — Wed, 08 Apr 2015 19:02:59 GMT

Amar,

Thank you for the help. Would I be able to use Set analysis for the If statements? Or just use one if statement for the dimension? Just wondering if there was a way to do it with less if statements.

Thanks again,

Justin

Re: Graph help - MTD vs. YTD

Anonymous — Wed, 08 Apr 2015 19:50:41 GMT

Actually with set analysis we wouldn't be able to compare to fields that's why I replaced it with sum if statements but if you have too many conditions I would normally create a flag in the script and use it in the expressions i.e.

- if ([CAT IND]='N' and [NEW RENEW IND]='NEW' ,1,0) as Flag_Condition1

- sum(Value*Flag_Condition1)

I would keep the expression as simple as possible and handle all the complexity in the backend as much as possible.

Thanks,

Re: Graph help - MTD vs. YTD

Anonymous — Wed, 08 Apr 2015 19:55:13 GMT

Amar,

That is helpful to know. Thank you so much for the help with this. I will give that a try with the flag to see how it works out. Seems easy and helpful. Could I do something similar for the plan years I have? Having a text with all numbers seems to have given me problems so far.

Thanks again,

Justin