YTD month

[smilingjohn]

HI All ,

I have table which has a date field ,

and i want to achaive the sum of all the values till previous month of the current selected month .

Iam trying with this below expression but its not working .Can someone please correct it .?

=Sum({$<PERIOD_NAME={$(=Max(PERIOD_NAME)-1)},[PERIOD_NAME]>}VALUE )

[sunny_talwar]

What is the format of data within PERIOD_NAME field?

[Gysbert_Wassenaar]

Maybe like this:

=Sum({$<PERIOD_NAME=, MyDate={'>=$(=YearStart(Max(MyDate)))<$(=MonthStart(Max(MyDate)))'}>}VALUE )

[Oleg_Troyansky]

The problem is most likely related to the format of the field PERIOD_NAME.

- If it's a string value, then you can't calculate Max value and subtract 1 from it.

- If it's a Dual value then the result of the calculation Max(PERIOD_NAME)-1 is a number, while the field itself is Dual. This mismatch doesn't work well in Set Analys.

If your field is Dual, then you can overcome the issue by using the Advance Search syntax, something like this:

=Sum({$<PERIOD_NAME={"=num(PERIOD_NAME)<=$(=Max(PERIOD_NAME)-1)"}>}VALUE )

Check out my new book QlikView Your Business. In the book, I describe in detail how to use Dates in Set Analysis and how to use Advanced Search functionality for a variety of analytical solution.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[miguelbraga · Accepted solution]

I think you might change a little bit of the expression for:

=Sum({$<PERIOD_NAME= {$(=Max(PERIOD_NAME) – 1)}>} VALUE)

If you don't want to chenge the value of the expression if another user makes selection in for example Year, Quarter, Month and/or Period you can change the expression to this:

=Sum({$<PERIOD_NAME= {$(=Max(PERIOD_NAME) – 1)}, Year = , Quarter = , Month = , Period = >} VALUE)

Hope it helps!

[smilingjohn]

I dont have year in my table , there are two date fields one is Date (30-01-2014) and the Period_Name(jan-2014).

In this format i have the date .

Gysbert I tried the expression but its not working , May be I am missing something , There is no master calendar in this . all i have is the two date fields

[sunny_talwar]

Try this may be:

=Sum({$<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DD.MM.YYYY') & '<' Date(MonthStart(Max(Date)), 'DD.MM.YYYY')))"}, Period_Name = >} VALUE)

Two things to make sure:

1) QlikView is case sensitive and there is a difference between PERIOD_NAME and Period_Name. Make sure to use the correct field name

2) Check if this -> ='>=' & Date(YearStart(Max(Date)), 'DD.MM.YYYY') & '<' Date(MonthStart(Max(Date)), 'DD.MM.YYYY')) in a text box object give you correct format and correct date range.

HTH

Best,

Sunny