topic Re: Monthly YTD Figure Challenge in QlikView

Monthly YTD Figure Challenge

Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I am getting Wrong Result when this Formula:

=SUM( {1<New_Date = {'>$(=Max((YearStart(New_Date)))) <=$(=Max(New_Date))'}>} value )

Required Modification in above Formula.

Need to achieve Monthly YTD Figures without using Rangesum(Above.....

Also not to use Accumulate or Full Accumulate setting.

Need output from Set Analysis or may be from backend Script.

If some faced such issue than kindly share the solution for the same.

I have Attached the sample qvw.

Regards

Cire

Re: Monthly YTD Figure Challenge

sunny_talwar — Thu, 06 Oct 2016 20:16:48 GMT

Check this out:

Table:

LOAD *,

Month(New_Date) AS New_Month,

Year(New_Date) AS New_Year,

MonthName(New_Date) as MonthYear;

LOAD *,

Date(Date#(date1,'MM/DD/YYYY'),'DD/MM/YYYY') AS New_Date

Inline [

date1, value

1/2/2016, 10

1/5/2016, 20

2/12/2016, 30

2/11/2016, 10

3/13/2016, 20

3/17/2016, 40

4/21/2016, 50

4/11/2016, 60

5/22/2016, 20

5/24/2016, 60

5/3/2016, 20

6/16/2016, 50

6/19/2016, 70

7/20/2016, 20

7/22/2016, 30

8/18/2016, 50

8/14/2016, 60

9/10/2016, 70

9/25/2016, 30

10/2/2016, 20

10/4/2016, 50

];

AsOfTable:

LOAD MonthYear as ReporMonthYear,

Month(MonthYear) as ReportMonth,

MonthName(AddMonths(MonthYear, -IterNo() + 1)) as MonthYear

Resident Table

While IterNo() <= 12;

Re: Monthly YTD Figure Challenge

Thu, 06 Oct 2016 20:29:17 GMT

Thanks Sir.

I can't use Iterno() in my Model.

Can I modify my expression to achieve desired result.

But how can I achieve Result from this Expression at Front end Chart:

=SUM( {1<New_Date = {'>$(=Max((YearStart(New_Date)))) <=$(=Max(New_Date))'}>} value )

What modifications I have to made in this.

Re: Monthly YTD Figure Challenge

jcs_2015 — Thu, 06 Oct 2016 20:37:16 GMT

A Master Calendar seems like it would be valuable as well assuming you are doing a lot of MTD/QTD/YTD expressions? With the "current month" set as a variable based on conditions in your dataset. Subsequently variations to current can also be variables too, i.e. last month $(CurrentMth-1) etc.

Re: Monthly YTD Figure Challenge

sunny_talwar — Thu, 06 Oct 2016 21:04:55 GMT

What is the issue with using IterNo()? I can give you a longer script, but IterNo() makes it smaller and easier to read (probably ). I can't think of making this work only using set analysis.