Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
flyhigh15
Contributor III
Contributor III

Pick Match , Set Analysis , Date range Current Month

I want Current Month Date range in pivot using Pick Match in a Set analysis

My MTD expression f:

pick(match(MYfield),'C 1','c2'),

num(Sum({<DATE={">=$(=Date(MonthStart(max(DATE)),'DD/MM/YYYY')) <=$(=Date(MonthEnd(max(DATE)),'DD/MM/YYYY'))"},Project={'ABC'},Tower={'C 1'}>}Amount))

num(Sum({<DATE={">=$(=Date(MonthStart(max(DATE)),'DD/MM/YYYY')) <=$(=Date(MonthEnd(max(DATE)),'DD/MM/YYYY'))"},Project={'ABC'},Tower={'C 2}>}Amount))

 

flyhigh15_0-1709120286264.png

 

Labels (3)
7 Replies
Antoine04
Partner - Creator III
Partner - Creator III

Hello,

It missing something in your match expression. The syntax is not correct.

Indeed, you just put 1 parameter in the match function. But actually, you need to put more, according to the function : match - fonction de script et fonction de graphique | Aide Qlik Sense sous Windows

What you should write is something like this :

pick(match(MYfield, 'C 1', 'c2'), YourExpressionForC1, YourExpressionForC2)

Hope it helps

Regards,

Antoine

flyhigh15
Contributor III
Contributor III
Author

Not working after doing above changes in the expression @Antoine04 

Antoine04
Partner - Creator III
Partner - Creator III

It should work. Here is a simple example :

Antoine04_0-1709199301153.png

Please send me your qvf file if you want me to try on your DATA.

Regards

flyhigh15
Contributor III
Contributor III
Author

Below is the revised Expression:

Pick(match(Tower,'C1','C2'),


num(Sum({<DATE={">=$(=Date(MonthStart(max(DATE)),'DD/MM/YYYY')) <=$(=Date(MonthEnd(max(DATE)),'DD/MM/YYYY'))"},Project={'ABC'},Tower={'C1'}>}Amount),

num(Sum({<DATE={">=$(=Date(MonthStart(max(DATE)),'DD/MM/YYYY')) <=$(=Date(MonthEnd(max(DATE)),'DD/MM/YYYY'))"},Project={'ABC'},Tower={'C2'}>}Amount))
)

 

Output: It's display previous Month & current month dates but it should display only current month date range in the column section of pivot table i.e. feb only @Antoine04 

flyhigh15_0-1709199714211.png

 

Antoine04
Partner - Creator III
Partner - Creator III

OK so you would like to remove the 0 into your pivot table ?

Have you tried to unbox "include null values" in the parameter of the table ?

When editing your set analysis expression, can you show me which date are evaluated for these expressions :

$(=Date(MonthStart(max(DATE)),'DD/MM/YYYY'))

$(=Date(MonthEnd(max(DATE)),'DD/MM/YYYY'))

Thanks

flyhigh15
Contributor III
Contributor III
Author

Yes , i have unboxed null values in date column , as well in other dimensions  as i want date excluded for previous months

below is the screenshot for date range  @Antoine04 

flyhigh15_0-1709201122640.png

 

Antoine04
Partner - Creator III
Partner - Creator III

Could you please share the qvf of it's not possible for you ?

Then I can check on my own Qlik.

Thanks