Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kunkumnaveen
Specialist
Specialist

How to write condition enabling in expression

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:  

Untitled.png

 

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 

Untit.png

 

 

After selecting  prod from category

Untitled1.png

 

 

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

Kindly suggest,how to write condition in expression.

 

Thanks

 

  

         

Labels (2)
28 Replies
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
Specialist
Specialist
Author

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 )

Untitled.png

 

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 )

Untitled1.png

 

sunny_talwar

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

image.png

kunkumnaveen
Specialist
Specialist
Author

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)

 

 

 

Untitled2.png

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
Specialist
Specialist
Author

Hello Sunny , Kindly change the Month ...

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

Please share the image after selection 

kunkumnaveen
Specialist
Specialist
Author

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

sunny_talwar

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
Specialist
Specialist
Author

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.......