topic Re: Set analysis to include current selection for all fields except one particular field in QlikView

Set analysis to include current selection for all fields except one particular field

sarathi_pm — Sat, 17 May 2014 19:30:44 GMT

Hi,

I have a dashboard which has 3 list boxes where the user can select.

1. 1.Snapshot_Date – Only one value can be selected at a time.

2. 2. Age

3. 3. Location

I need to show the #users as current month vs Previous month based on the snapshot date selection. I have created 2 variables to store the current month and the previous month values

v_Max_Snapshot_Date =Date(SNAPSHOT_DATE,'MM-DD-YYYY')

v_Max-1_Snapshot_Date =Date(addmonths((SNAPSHOT_DATE),-1),'MM-DD-YYYY')

I have created a straight table where I have written set analysis expression as shown below

Current Month: =Count(DISTINCT{$<SNAPSHOT_DATE={'$(v_Max_Snapshot_Date)'}>}[Users])- this works fine

Previous Month: =Count(DISTINCT{$<SNAPSHOT_DATE={'$(v_Max-1_Snapshot_Date)'}>}[Users]) - shows 0

The error i am facing is, the previous month value is displayed as 0. If I change it to 1, it shows the overall user count for previous month, but its not changing based on the Age or Location filter when selected.

Any idea how to exclude the snapshot date alone from current selection and include the Age and location in the set analysis to be taken based on the current selection in the previous month users calcuation?

Re: Set analysis to include current selection for all fields except one particular field

whiteline — Sat, 17 May 2014 19:46:05 GMT

Hi.

Remove '-' from the variable name.

Your set analysis is fine. Using $ as the basis you keep Age, Location and override Snapshot_Date.

Re: Re: Set analysis to include current selection for all fields except one particular field

sarathi_pm — Sat, 17 May 2014 20:33:57 GMT

Thanks for your response, But i am not able to get it working.

I have attached a sample file for your reference. COuld you please correct me where i am making mistake so that i am able to show the previous month values based on the selection

Re: Set analysis to include current selection for all fields except one particular field

whiteline — Sun, 18 May 2014 07:48:24 GMT

Hi.

It works perfect for me.

There is no data for previous month for the selection provided (India, 22) and the expression returns 0.

Choose France and you'll see the difference.

Remove 1 from set analysis expression for the previous period.