topic Re: Calculated Dimension and Expression Issue in QlikView

Calculated Dimension and Expression Issue

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have below expression in my calculated dimension for my table, basically it looks into dimension table for same virtual queue and call date between startdate and enddate then take forecast_group.

if(virtual_queue=dim_vq_fg_bu_vq and cal_date >= startdate and cal_date <= enddate,dim_vq_fg_bu_forecast_group, 'NA')

In my expression, I just count the session keys for above dimension. The table shows correct forecast_group, but also show NA, and NA is the total of forecast_group (Please see the picture below). I want to count session keys for each forecast group and if there are no matches, label them as NA of forecast_group and count their session key.

Would someone please tell me how to accomplish this?

Thank you for your inputs.

Re: Calculated Dimension and Expression Issue

sunny_talwar — Mon, 06 Mar 2017 17:35:39 GMT

Would you be able to share a sample to look at the issue?

Re: Calculated Dimension and Expression Issue

Mon, 06 Mar 2017 18:11:56 GMT

Hi Sunny,

Thank you for your response.

I have attached the sample data in original post and these are 72 records in the picture.

Re: Calculated Dimension and Expression Issue

sunny_talwar — Mon, 06 Mar 2017 19:29:24 GMT

What is dim_vq_fg_bu_vq here? I don't see this in the Excel file you have attached

Re: Calculated Dimension and Expression Issue

Mon, 06 Mar 2017 19:48:45 GMT

sorry, that is the virtual_queue.

Re: Calculated Dimension and Expression Issue

sunny_talwar — Mon, 06 Mar 2017 20:01:16 GMT

What are you doing with this if statement

One field is virtual_queue and the other is dim_vq_fg_bu_vq field... where is the second one?

Re: Calculated Dimension and Expression Issue

Mon, 06 Mar 2017 20:17:52 GMT

The dim_vq_fg_bu_vq would be the virtual_queue from dimension table. I have updated the Excel file with correct field name.

Basically, in the excel file, if we join the data and dimension table together. I want the session key (0B0CA5A3-E9AF-47C1-EF94-8B307BA14C7B) to get customer forecast group, because its virtual queue can be found in Dimension table and the cal date is between start and end date. Another example, the 1CAFE530-83FD-4A9C-2E91-24E27063F2B7 session key would get unlicensed because the cal_date falls between start and end date of unlicensed.