topic Re: Logic Help in QlikView

Logic Help

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

Hey Guys,

Need Some simple logic syntax help

I have to show MTD so i did this like below

>=monthstart(max(date)) <= Monthend(max(date))

which is working fine..

but in my data the max Date is future date like after 2 month in my case.

so what i want is by default dashboard shud work on today() condition and if user select anything than it shud work on my normal condition described above.

Today condition i hope would be like this.

>=monthstart(today()) <= Monthend(today()).

i think i can write the condition using Getfieldselection, but i need syntax help, or any other input appreaciated

Re: Logic Help

MK_QSL — Wed, 27 Apr 2016 09:00:07 GMT

Sum({<Date = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>}Value)

This will give you Month To Date

If you want MonthStart to MonthEnd, use below

Sum({<Date = {">=$(=Date(MonthStart(Today())))<=$(=Date(MonthEnd(Today())))"}>}Value)

Re: Logic Help

sunny_talwar — Wed, 27 Apr 2016 09:05:19 GMT

May be this:

{<date = {"$(='>=' & MonthStart(If(GetSelectedCount(Date) = 0, Today(), Max(date))) & '<=' & MonthEnd(If(GetSelectedCount(Date) = 0, Today(), Max(date))))"}>}

Re: Logic Help

abhaysingh — Wed, 27 Apr 2016 10:38:22 GMT

Hi Manish,

What i want is by default it shud give me current month to Date and if user select any backdate month or year , user shud able to see the data for selected month as MTD and Respected back month as LMTD

Re: Logic Help

MK_QSL — Wed, 27 Apr 2016 10:47:55 GMT

I assumed that you are giving Month and Year fields to user for selection.

=If(GetSelectedCount(InvoiceYear) = 0 and GetSelectedCount(InvoiceMonth) = 0, SUM({<InvoiceDate = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>}Sales),

SUM({<InvoiceYear, InvoiceMonth, InvoiceDate = {">=$(=Date(MonthStart(MakeDate(Max(InvoiceYear),Max({<InvoiceYear = {'$(=Max(InvoiceYear))'}>}InvoiceMonth)))))<=$(=Date(MonthEnd(MakeDate(Max(InvoiceYear),Max({<InvoiceYear = {'$(=Max(InvoiceYear))'}>}InvoiceMonth)))))"}>}Sales))

Change...

1) InvoiceYear... InvoiceMonth.... and Sales Field according to your application fieldnames.

2) If you want to ignore some fields like Week, Quarter, add them in Set Analysis field as Week=, Quarter=

For Previous Month ... use

=If(GetSelectedCount(InvoiceYear) = 0 and GetSelectedCount(InvoiceMonth) = 0, SUM({<InvoiceDate = {">=$(=Date(MonthStart(Today(),-1)))<=$(=Date(AddMonths(Today(),-1)))"}>}Sales),

SUM({<InvoiceMonth = , InvoiceYear = , InvoiceDate = {">=$(=Date(MonthStart(MakeDate(Max(InvoiceYear),Max({<InvoiceYear = {'$(=Max(InvoiceYear))'}>}InvoiceMonth)),-1)))<=$(=Date(MonthEnd(MakeDate(Max(InvoiceYear),Max({<InvoiceYear = {'$(=Max(InvoiceYear))'}>}InvoiceMonth)),-1)))"}>}Sales))

Re: Logic Help

abhaysingh — Wed, 27 Apr 2016 11:34:03 GMT

Hi Manish,

these condition perfectly working on default Current and Last month, but when i m selecting any backdate its not working..

Any suggestion?

Re: Logic Help

MK_QSL — Wed, 27 Apr 2016 11:41:55 GMT

What is your selection fields? What format they are? Are they derived from date fields?

Re: Logic Help

sunny_talwar — Wed, 27 Apr 2016 11:56:55 GMT

Have you tried what I proposed may be?