topic Re: Show values in YTD basis? in QlikView

Show values in YTD basis?

dmohanty — Wed, 23 Apr 2014 05:53:56 GMT

Hi All,

Currently I am showing the Total Revenue by Quarter in a Bar Graph, means for each Quarter, I have some Revenues.

Now my requirement is to show the Revenue in a YTD basis in a Bar Graphy and compare the both charts.

How to calculate in YTD basis? Please guide.

Let say my table has this values:

Quarter	Revenue (Mn $)
Q1	150
Q2	100
Q3	120
Q4	180

Re: Show values in YTD basis?

manojkulkarni — Wed, 23 Apr 2014 06:17:31 GMT

Do you have date field or Month Year field apart from Quarter ?

Re: Show values in YTD basis?

MK_QSL — Wed, 23 Apr 2014 06:24:39 GMT

YTD can be calculated by

Dimension

YourDateField

Tick Suppress When Value is NULL

Expression

SUM({$<YourDateField = {'<=$(=Today())'}>}Revenue)

Re: Show values in YTD basis?

dmohanty — Wed, 23 Apr 2014 06:42:20 GMT

Apart from Quarter, I have a separate field called Month with values like Jan-13, Feb-13, Mar-13....Dec-13

Re: Show values in YTD basis?

dmohanty — Wed, 23 Apr 2014 06:44:33 GMT

Hi Manish,

I dont have an exact date field, but have a Month field with values Jan-13, Feb-13, Mar-13....Dec-13.

Could you please show me an example, with sample data shared above?

Re: Show values in YTD basis?

manojkulkarni — Wed, 23 Apr 2014 06:57:43 GMT

If date field is not present, then you can't call the value as YTD. Still want to continue, then using your Month Year field create date & use InYearToDate function to create flag inside your script.

Or you can use expression suggested by Manish...

Re: Show values in YTD basis?

Wed, 23 Apr 2014 07:04:54 GMT

Hi DMohanty,

You want to show YTD in same graph or seperate graph....

Re: Show values in YTD basis?

dmohanty — Wed, 23 Apr 2014 07:23:46 GMT

Saranya,

Wanted to show in a separate Graph. But which ever is feasible, is better. Pleas guide.

Re: Show values in YTD basis?

Wed, 23 Apr 2014 07:33:18 GMT

1.In Quarterwise Revenue graph remove the dimension.

then give the text 'YTD' in AddCalculatedDimension,It will display YTD in seperate graph

2.In Existing Quarterwise Revenue graph,under Presentation tab

enable Show total option,It will display YTD in same graph

Hope it will help......

Re: Show values in YTD basis?

dmohanty — Thu, 24 Apr 2014 06:07:24 GMT

Hi All,

I just have a Quarter field and a Month field (as mentioned above). Still am unable to create a Date field from those and calculate the YTD.

Could someone show with an example please?

Re: Re: Show values in YTD basis?

Fri, 25 Apr 2014 04:00:49 GMT

Hi DMohanty,

PFA for your reference,

Hope it will help...........

Re: Re: Show values in YTD basis?

jagan — Fri, 25 Apr 2014 04:20:25 GMT

Hi,

PFA file for solution.

Regards,

Jagan.

Re: Show values in YTD basis?

dmohanty — Fri, 25 Apr 2014 06:21:31 GMT

Thanks a lot Jagan and Saranya,

It really helped and I learnt a new thing as well.

Many thanks again.