How to calculate YTD value that does not change with changes in year and month selection?

[snehamahaveer]

Hi Everyone, I have two charts one which displays value for a particular month and another which shows YTD value of sales amount. But when I change the selection of month the YTD value changes as well. How do I create  the formula so that it remains constant. I used sum({Sales}) but that does not make the value constant when a month is selected. Your help is much appreciated. Thanks in advance.

[sinanozdemir]

Hi,

Maybe try like this:

Sum({1}Sales) this way total of sales won't change.

[qlikviewwizard]

Hi,

Please check this document.

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

[SreeniJD]

Hi Sneha,

As per the Qlikview associativity, with each selection the corresponding data will change, if not that would be an issue. Coming to your scenario, the charts which have month and YTD respectively has to change respective their selection.

With the very basic set analysis you can achieve this..

Create two different columns(facts)... one for month and another one for YTD and in YTD column apply the below expression

=sum({1}Sales)

If you want to perform any aggregation you can do that

Happy to help!

Sreeni

[#5]

If you have Month ID and Month Name, use below expression for YTD Value. This will not change on month selection

Sum({$<MonthID = {"<=$(=Max(MonthID))"},Year = {$(=Max(Year))},Quarter = ,MonthName = >} Sales))

Incase you have only Month Name then Month Id can also created using below script

MapTable: 

Mapping LOAD 

RowNo() as MonthID, 

Month(AddMonths((Today()),RowNo()+7)) as MonthName 

AutoGenerate 12; 

FactTable: 

LOAD *, ApplyMap('MapTable',MonthID) as MonthName 

Inline [ 

MonthID 

1 

2 

3 

4 

5 

6 

7 

8 

9 

10 

11 

12 

]; 

[Anonymous]

Hi Sneha,

  Try this  Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Today()))<=$(=Today())’}>} Sales )

[engishfaque]

Dear Sneha,

Sum ({<Year = {'$(=Max(Year))'}, Date = {'<= $(=Max(Date)'}, Month = >} Sales)

Please note, if you are interested to ignore any filed selection then just write down Field Name with Equal Sign '=' into set analysis such as (Quarter =, Week 😃 etc. Month = is also given into given above set analysis which means if you select/click on Month then no change will occur.

Kind regards,

Ishfaque Ahmed