Pick Match , Set Analysis , Date range Current Month

[flyhigh15]

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

[Antoine04]

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]

Not working after doing above changes in the expression @Antoine04 

[Antoine04]

It should work. Here is a simple example :

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

Regards

[flyhigh15]

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 

[Antoine04]

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]

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 

[Antoine04]

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

Then I can check on my own Qlik.

Thanks