How to write condition enabling in expression

[kunkumnaveen]

Hello All,

i Need help in doing a condition enabling in a chart , in below combo chart by default i have to show as below

Dimension: YEARMONTH

Expression: 1)Prod:    Sum({<Month,Year,Date={">=$(=date(monthstart(addmonths(max(Date),-5)),'DD-MM-YYYY'))<=$(=date(((max(Date))),'DD-MM-YYYY'))"}>}p_input)

                        2)Sale:      Sum({<Month,Year,Date={">=$(=date(monthstart(addmonths(max(Date),-5)),'DD-MM-YYYY'))<=$(=date(((max(Date))),'DD-MM-YYYY'))"}>}p_d_op)

Combo:  

so far so good,

Now my requirement is to do a condition enabling , I have created a inline field called Category which as values prod,sale,Brand and in each expression i have added condition as shown below

Combo Chart:

Dimension: YEARMONTH

EXpression :1)   if(Getselectedcount(Category) =0 or Category='prod',

Sum({<Month,Year,Date={">=$(=date(monthstart(addmonths(max(Date),-5)),'DD-MM-YYYY'))<=$(=date(((max(Date))),'DD-MM-YYYY'))"}>}p_input)

)

Before selecting Category 

After selecting  prod from category

I am getting only Selected Year, Month prod values even after ignoring Year,Month

Kindly suggest,how to write condition in expression.

Thanks

[sunny_talwar]

Try this

prod

If(SubStringCount(Concat(DISTINCT {1<Category = p(Category)>} '|' & Category & '|'), '|prod|'),
	Sum({<Month, Year, Date = {">=$(=Date(MonthStart(Max(Date),-5), 'DD-MM-YYYY'))<=$(=Date(Max(Date), 'DD-MM-YYYY'))"}>} p_input)
)

sale

If(SubStringCount(Concat(DISTINCT {1<Category = p(Category)>} '|' & Category & '|'), '|sale|'),
	Sum({<Month, Year, Date = {">=$(=Date(MonthStart(Max(Date), -5), 'DD-MM-YYYY'))<=$(=Date(Max(Date), 'DD-MM-YYYY'))"}>} p_d_OP)
)

[kunkumnaveen]

Hello Sunny, Thank you very much ,appreciated your work ..For some reason i was not able to open your Qvf file.

So what i have done is ,i have used the above expression in mywork .

when Month =Dec and Year=2019 and Category = prod and Category = sale (Getting Required Output )

If i change the months to another then issue persist 

For example

when Month =Jun  and Year=2019 and Category = prod and Category = sale (Issue )

[sunny_talwar]

I won't really know what you might be doing differently, but it works for me

[kunkumnaveen]

Continuation....

When i remove condition from set analysis and Select Only Year =2019and Month=Jun

Expression : Sum({<Month,Year, Date = {">=$(=Date(MonthStart(Max(Date),-5), 'DD-MM-YYYY'))<=$(=Date(Max(Date), 'DD-MM-YYYY'))"}>} p_input)

so even with condition enabling ,the out put should be same as above 

[sunny_talwar]

Make sense, but not sure why it doesn't work for you when it does for me.

[kunkumnaveen]

Hello Sunny , Kindly change the Month ...

I mean select Month other than Dec...for example Month=Jun

Please share the image after selection 

[kunkumnaveen]

Sunny , i can see your Month is Dec , Please change the Month=Jun or any other random month other then Dec

[sunny_talwar · Accepted solution]

New expressions

prod

If(SubStringCount(Concat(DISTINCT TOTAL {1<Category = p(Category)>} '|' & Category & '|'), '|prod|'),
	Sum({<Month, Year, Date = {">=$(=Date(MonthStart(Max(Date),-5), 'DD-MM-YYYY'))<=$(=Date(Max(Date), 'DD-MM-YYYY'))"}>} p_input)
)

sale

If(SubStringCount(Concat(DISTINCT TOTAL {1<Category = p(Category)>} '|' & Category & '|'), '|sale|'),
	Sum({<Month, Year, Date = {">=$(=Date(MonthStart(Max(Date), -5), 'DD-MM-YYYY'))<=$(=Date(Max(Date), 'DD-MM-YYYY'))"}>} p_d_OP)
)

[kunkumnaveen]

HI Sunny,

Thank you Very Very Much ............Its working ,i was behind this requirement since from last 10 days and i am sure if u haven't help me in this,I couldn't  have fix this .

Thanks a lot again.......