topic Re: YTD month in QlikView

YTD month

smilingjohn — Thu, 15 Oct 2015 15:33:03 GMT

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 )

Re: YTD month

sunny_talwar — Thu, 15 Oct 2015 15:36:09 GMT

What is the format of data within PERIOD_NAME field?

Re: YTD month

Gysbert_Wassenaar — Thu, 15 Oct 2015 15:37:46 GMT

Maybe like this:

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

Re: YTD month

Oleg_Troyansky — Thu, 15 Oct 2015 15:41:44 GMT

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

Re: YTD month

miguelbraga — Thu, 15 Oct 2015 15:42:20 GMT

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!

Re: YTD month

smilingjohn — Thu, 15 Oct 2015 15:46:26 GMT

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

Re: YTD month

sunny_talwar — Thu, 15 Oct 2015 15:57:34 GMT

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