topic Re: How to Achieve this in QlikView

How to Achieve this

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

I am attaching files

if yu see the qvw file I am calculating YTD in script for month wise.that is working fine. but if I take age as dimension it is not working

its showing wrong values.

any ideas

thanks in advance

Re: Calculate YTD in Script

Kushal_Chawda — Mon, 28 Aug 2017 13:05:21 GMT

Create YTD Flag like this

Data:

LOAD *,

YearToDate(Period1,0,4)*-1 as Flag

FROM table;

Now use expression like below

=sum({<Flag={1}>} Value)

Re: Calculate YTD in Script

gautik92 — Mon, 28 Aug 2017 13:14:46 GMT

will it work if I select a month

Re: Calculate YTD in Script

gautik92 — Mon, 28 Aug 2017 13:16:07 GMT

Supppose if I select July 2017 it shud show value summing up from April 2017 to July 2017

Re: Calculate YTD in Script

Kushal_Chawda — Mon, 28 Aug 2017 13:16:48 GMT

Yes. It will work

Data:

LOAD *,

monthname(Period1) as MonthYear,

YearToDate(Period1,0,4)*-1 as Flag

FROM table;

Re: Calculate YTD in Script

Kushal_Chawda — Mon, 28 Aug 2017 13:18:39 GMT

But why you want Apr 17 to Jul 17 when jul 17 is selected, it should ideally display only Jul 17 no?

Re: How to Achieve this

gautik92 — Mon, 28 Aug 2017 13:21:54 GMT

yes if I select july 17 it shud sum up value from apr 17 to till date and show

Re: How to Achieve this

Kushal_Chawda — Mon, 28 Aug 2017 13:29:28 GMT

You can exclude the selection of your other periods for YTD,

Data:

LOAD *,

monthname(Period1) as MonthYear,

YearToDate(Period1,0,4)*-1 as Flag

FROM table;

expression

Sum({<Flag={1},MonthYear>}Leavers)

Re: How to Achieve this

gautik92 — Mon, 28 Aug 2017 13:37:48 GMT

this is not working

Re: How to Achieve this

vinieme12 — Mon, 28 Aug 2017 13:45:02 GMT

See this post

The As-Of Table

Calculating rolling n-period totals, averages or other aggregations

Re: How to Achieve this

Kushal_Chawda — Mon, 28 Aug 2017 13:48:22 GMT

Use below expression, instead of creating Flag in script

Sum({<Period1={">=$(=Yearstart(today(),0,4))<=$(=max(Period1))"},Year,MonthYear>}Leavers)