topic Set Analysis in App Development

Set Analysis

madnanansari — Thu, 19 Oct 2017 07:07:32 GMT

I have the below function which calculate the number of days till yesterday for the selections made in the filter for Year and month.

If there are no selections; it gives the number of days till yesterday during this month. For example today is 19th of october then this function will give 18. (the first line).

Where there is a selection; let say year 2017; it will give the number of days during the year till yesterday. and viceversa. (second line).

We have upgraded our system from 3.1 to september 2017 (b/c of some bugs to cover). Now the below function is giving '0' for all selections. please help to rectify.

If(getselectedcount([Year])<1 and getselectedcount([Month Year])<1,

Count({$<[Date] = {"<=$(=Today()-1)"},[Year]=,[Month Year]=,[MonthID]= {"$(=((Year(Today()) -1)*12) + Num(Month(Today())))"}>} [Date])

Count({$<[Date] = {"<=$(=Today()-1)"}>} [Date])

)

Re: Set Analysis

isorinrusu — Thu, 19 Oct 2017 07:20:44 GMT

Hi Mohammad,

Probably the Date field format has been messed up with the upgrade. What's the Date field format?

Regards,

Sorin.

Re: Set Analysis

madnanansari — Thu, 19 Oct 2017 07:28:53 GMT

Date is collected from the QVD file by the following:

Floor(SOH_DATE) as [Date],

Re: Set Analysis

isorinrusu — Thu, 19 Oct 2017 07:38:28 GMT

Try to change it to Date(Floor(SOH_DATE) as Date.

The floor function delivers an integer (for example 41382).

Or maybe

Regards,

Sorin.

Re: Set Analysis

madnanansari — Thu, 19 Oct 2017 07:39:19 GMT

i tried. it also bring '0'.

Re: Set Analysis

pradosh_thakur — Thu, 19 Oct 2017 07:53:42 GMT

try this

If(getselectedcount([Year])<1 and getselectedcount([Month Year])<1, Today() - MonthStart(Today())-1,Today() - YearStart(Makedate(Year))-1)

regards

Pradosh

Re: Set Analysis

madnanansari — Thu, 19 Oct 2017 08:10:35 GMT

this will not work in case if i select multiple years

Re: Set Analysis

pradosh_thakur — Thu, 19 Oct 2017 08:19:47 GMT

use min(year) instead

If(getselectedcount([Year])<1 and getselectedcount([Month Year])<1, Today() - MonthStart(Today())-1,Today() - YearStart(Makedate(min(Year)))-1)

regards

Pradosh

Re: Set Analysis

madnanansari — Thu, 19 Oct 2017 09:31:38 GMT

this will not work in case if i select month only instead of year

Re: Set Analysis

pradosh_thakur — Thu, 19 Oct 2017 09:38:25 GMT

your expression was working for month selection too?

may be this

If(getselectedcount([Year])<1 and getselectedcount([Month Year])<1 or count(p(Month))=1, Today() - MonthStart(Today())-1,Today() - YearStart(Makedate(min(Year)))-1)

Re: Set Analysis

madnanansari — Thu, 19 Oct 2017 09:41:39 GMT

it works for lot of things....that's why i dont like to change the formula....i just need correction in the formula

Re: Set Analysis

madnanansari — Thu, 19 Oct 2017 09:45:39 GMT

this is the actual formula:

If(getselectedcount([Year])<1 and getselectedcount([Half Year])<1 and getselectedcount([Year Quarter])<1 and getselectedcount([Month Year])<1 and getselectedcount([Hijri Year])<1 and getselectedcount([Hijri Month Year])<1,

Count({$<[Date] = {"<=$(=Today()-1)"},[Hijri Year]=,[Hijri Month Year]=,[Year]=,[Half Year]=,[Year Quarter]=,[YearMonth No]=,[Month No]=,[Month Year]=,[Month]=,[MonthID]= {"$(=((Year(Today()) -1)*12) + Num(Month(Today())))"}>} [Date]),

Count({$<[Date] = {"<=$(=Today()-1)"}>} [Date])

)

Re: Set Analysis

pradosh_thakur — Thu, 19 Oct 2017 10:47:13 GMT

try this once

count({1<Date={">= $(=min([Date])) <= $(=today()-1)"}>}[Date[))

)

you can replace the first expression of if condition simply as day(today())-1 or something similar to this.

Re: Set Analysis

madnanansari — Thu, 19 Oct 2017 13:54:22 GMT

it dont work