Match function issue

[raju_salmon]

Hi All,

Please find the attached QVW.

I have created "Expanded Sat and Sun Count YearWise" Chart, it should show the count of sat and sun for the each year.

Ex: 2004 - 52+52=104

If i see expanded chart, it is showing 52 Sat and 52 Sun When we collapsed it, it is showing 366.

Because it is also calculating NULL values, which i have suppressed.

Can we exclude NULL values (262) from calculation?

I have created WeekStatus Field using Match Function. Is there any function which i can use here to do not calculate other than SAT and SUN? Please see the script for this field.

I have lot of requirement like this to use Match function to select few values from main field to derive new field, and i need to use those fields in PIVOT table... due to NULL values, i am not getting correct figure while collapsing the pivot table.

Thanks,

Raju

[swuehl · Accepted solution]

Try an expression

=Count({<WeekStatus = {"*"}>}Date)

Then you can also disable 'Suppress when values is NULL' on dimension tab

[swuehl]

Or even more simple:

=Count(WeekStatus)

[lironbaram]

hi

have a look at the attach example

[raju_salmon]

Thank you for the reply.

I have lot of complex set analysis expression for other requirements, so i would to do these filters at script level and using those new fields as dimensions of pivot tables.

So, any suggestions at script level?

Attached one is just an example i have provided.

Thanks.

[raju_salmon]

Thank you for the reply.

I have lot of complex set analysis expression for other requirements, so i would to do these filters at script level and using those new fields as dimensions of pivot tables.

So, any suggestions at script level?

Attached one is just an example i have provided.

Thanks.

[swuehl]

Not sure if I understand, I think you just need to count the correct field:

=Count( WeekStatus)

[raju_salmon]

I have provided an example Swuehl since i have confidential data.

I need to create a pivot table with few dimensions... and many measures with set analysis.

and those dimension i need to derive (since 10-20 selected values i need to keep, so using match function).

Hence i derived those fields at script level using match function. However for remaining values it is considering as NULL at PIVOT table.

That is the issue. To understand this, i made sample QVW with Calendar.

[swuehl]

And can you point me to a scenario using your sample or an updated sample where the two solutions I've suggested won't work?

[raju_salmon]

Your Solution works fine Swuehl when my KPI is related to dimension.

My KPI calculations are completely different than dimension... i am using these dimension to just do filters. So, i am looking for script level filters.

Only one pivot table with 20+ kpi has this requirement, hence not using where clause in the script.

I will try anyway.

Thanks.