Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor III

Filter Counting Based on Month selection

Hello!

I have a filter that is calculating the count of inventory:

Expression: Count([Inventory Bascom Item Type])

Frequency Expression: Bascom Inspection Frequency

Month Expression: Month

And I want it to change based on a Frequency OR Month selection. SO if I select frequency "monthly" it does count only inventory that has a monthly inspection but I also want it to count if I select month Feb it counts the inventory twice, and if I select March it counts it 3 times. Can that be done?!

Thank you!!!

1 Solution

Accepted Solutions
Highlighted
Honored Contributor III

Re: Filter Counting Based on Month selection

try putting this in a text box

Num(Month(Date#(Max([Month]),'MMM'))) and see if you get a number


Try


If(WildMatch([Bascom Inspection Frequency],'monthly'),Count([Inventory Bascom Item Type])

,

Count([Inventory Bascom Item Type]) * Num(Month(Date#(Max([Month]),'MMM')))

)



or


If(WildMatch([Bascom Inspection Frequency],'monthly'),Count([Inventory Bascom Item Type])

,

Count([Inventory Bascom Item Type]) * Num(Max(Month))

)


View solution in original post

6 Replies
Highlighted
Honored Contributor III

Re: Filter Counting Based on Month selection

May be like

If(frequency='Monthly,Count([Inventory Bascom Item Type])

,

Count([Inventory Bascom Item Type]) * Num(Month(Date#('$(vMonth)','MMM')))

)

Highlighted
Contributor III

Re: Filter Counting Based on Month selection

Thanks Sasidhar but that doesn't seem to work either

Highlighted
Honored Contributor III

Re: Filter Counting Based on Month selection

Hi

Have you tried the if with the expressions values related to your model?...I am guessing here

If(WildMatch([Bascom Inspection Frequency],'monthly'),Count([Inventory Bascom Item Type])

,

Count([Inventory Bascom Item Type]) * Num(Month(Date#([Month],'MMM')))

)


If this does not work, pl attach a sample

Highlighted
Contributor III

Re: Filter Counting Based on Month selection

I think adding the "Wildcard" part works

The thing is I also need it to count based on the months that are selected. It sounds a bit complicated but when a Month is selected let say March, then I want the inventory to be counted *3 since it's three months. But if Frequency Monthly or Quarterly or Annually is selected it counts the inventory for those.

Does that make sense?

Highlighted
Honored Contributor III

Re: Filter Counting Based on Month selection

try putting this in a text box

Num(Month(Date#(Max([Month]),'MMM'))) and see if you get a number


Try


If(WildMatch([Bascom Inspection Frequency],'monthly'),Count([Inventory Bascom Item Type])

,

Count([Inventory Bascom Item Type]) * Num(Month(Date#(Max([Month]),'MMM')))

)



or


If(WildMatch([Bascom Inspection Frequency],'monthly'),Count([Inventory Bascom Item Type])

,

Count([Inventory Bascom Item Type]) * Num(Max(Month))

)


View solution in original post

Highlighted
Contributor III

Re: Filter Counting Based on Month selection

If(WildMatch([Bascom Inspection Frequency],'monthly'),Count([Inventory Bascom Item Type])

,

Count([Inventory Bascom Item Type]) * Num(Max(Month))

)


That one WORKED! I would have never figured that out but I understand what you did. Thank you so much!!!!