Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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)
)
Highlighted
Specialist
Specialist

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

 

Highlighted

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

image.png

Highlighted
Specialist
Specialist

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 

Highlighted

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

Highlighted
Specialist
Specialist

Hello Sunny , Kindly change the Month ...

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

Please share the image after selection 

Highlighted
Specialist
Specialist

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

Highlighted

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

View solution in original post

Highlighted
Specialist
Specialist

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