topic Re: Expression help in QlikView

Expression help

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

Hi All,

i want to add some bucketing in my expression ..

This is my expression

SUM({$<FinancialYear=,Quarter=,MonthName=,[Date] ={"<=$(=MonthEnd(Max([Date])))"}>}[Sale])

And I want to add below condition in above expression

=if((num((num(vDateForAgeing)-[Post Date ]),'#######0') )<=180 ,'Below Six Month',

if(num((num(vDateForAgeing)-[Post Date ]),'#######0')>=180.1,'Above 181',

0))

Thanks

Re: Expression help

senpradip007 — Wed, 28 Sep 2016 05:00:57 GMT

Could you explain vDateForAgeing ?

Re: Expression help

Anonymous — Wed, 28 Sep 2016 05:08:13 GMT

vDateForAgeing

=IF(ISNULL(GetFieldSelections(MonthName))=0 OR ISNULL(GetFieldSelections(FinancialYear))=0,DATE(MonthEnd(MAX(Date))),DATE(Today()))

Re: Expression help

Anonymous — Wed, 28 Sep 2016 05:23:33 GMT

What exactly you want to achieve by adding this condition in your expression?

Re: Expression help

jagan — Wed, 28 Sep 2016 05:36:30 GMT

Hi,

Try this

=if((num((num(vDateForAgeing)-[Post Date ]),'#######0') )<=180 AND num((num(vDateForAgeing)-[Post Date ]),'#######0')>=180.1,

SUM({$<FinancialYear=,Quarter=,MonthName=,[Date] ={"<=$(=MonthEnd(Max([Date])))"}>}[Sale]))

Regards,

Jagan.

Re: Expression help

jonathandienst — Wed, 28 Sep 2016 05:44:44 GMT

If vDateForAgeing and Post Date are numeric QV date values, then this is enough:

...

If(vDateForAgeing - [Post Date ] <= 180, 'Below Six Month',

If(vDateForAgeing - [Post Date ] >=180.1, 'Above 181', 0)) As Buckets,

...

If there are more buckets, you might want to consider interval matching rather than a nested if. Does [Post Date ] really have a trailing space in the field name?

Re: Expression help

Anonymous — Wed, 28 Sep 2016 06:04:02 GMT

Hi Jagan,

I need to create two separate column for this .

Thanks

Re: Expression help

Anil_Babu_Samineni — Wed, 28 Sep 2016 06:06:41 GMT

Perhaps This

=if(num(num(vDateForAgeing)-[Post Date]),'#######0') <= Num(180),'Below Six Month', num((num(vDateForAgeing)-[Post Date ]),'#######0')>=180.1,'Above 180.1', SUM({$<FinancialYear=,Quarter=,MonthName=,[Date] ={"<=$(=MonthEnd(Max([Date])))"}>}[Sale]))

If you use this, What you are getting in your application, Would you provide sample application which demonstrates the o/p. If this gives number let me know. Better approach is want to see.

LET vDateForAgeing = IF(ISNULL(GetFieldSelections(MonthName))=0 OR ISNULL(GetFieldSelections(FinancialYear))=0,DATE(MonthEnd(MAX(Date))),DATE(Today()))

Re: Expression help

miskin_m — Wed, 28 Sep 2016 06:23:31 GMT

Hi Vijay,

You need to add your aging expression as a calculated dimension to create two columns under dimension tab.

Hope this is what you want.

Re: Expression help

Anonymous — Wed, 28 Sep 2016 06:37:14 GMT

Hi Vijay,

In your expression, have you tried to floor the expression. Something like:

=if((num((num(floor(vDateForAgeing))-[Post Date ]),'#######0') )<=180 ,'Below Six Month',

if(num((num(floor(vDateForAgeing)-[Post Date ])),'#######0')>=180.1,'Above 181',

0))

Re: Expression help

Anonymous — Wed, 28 Sep 2016 08:21:15 GMT

Hi All,

I need Bucket ageing sale for above 6 six month and Below Six month....

Thanks

Re: Expression help

Anil_Babu_Samineni — Wed, 28 Sep 2016 08:25:37 GMT

Bucket Creation In List Box

Re: Expression help

Anonymous — Wed, 28 Sep 2016 08:38:53 GMT

I have created like this but i want in this as expression

Like in my one repression I want sum(sale) in 2nd expression sum(actual sale < 180 days ) and 3rd expression sum(actual Sale > 180 days)

Thanks