topic Dynamic Dimension in QlikView

Dynamic Dimension

prrajendran — Sat, 16 Nov 2024 20:51:27 GMT

Hi Experts,

i have a straight table which has Reference number as dimension and two expressions.

my first expression brings the occurrences of the reference number for the selected month

second expression brings the occurrences of the reference number for the selected month and past 2 months from selection.

the expressions working fine.

but i want my dimension to be filtered only for the selected month.

i.e., 'RefA' reference number can be in multiple months

'RefB' in just the selected month.

then i want to display only 'RefB' in my dimension. how can i do this. please help

Re: Dynamic Dimension

anushree1 — Thu, 16 May 2019 09:27:39 GMT

Could you please put the Sample input and desired output here

Re: Dynamic Dimension

prrajendran — Thu, 16 May 2019 10:12:21 GMT

Input:
Date AcNo Reference Number
03/01/2019 030245 4024257978
06/01/2019 060470 4024257978
08/01/2019 080510 4024257978
09/01/2019 090917 4024257978
27/01/2019 270107 4024257978
05/02/2019 051406 4024257978
07/02/2019 070309 4024257978
09/02/2019 091672 4024257978
18/02/2019 180504 4024257978
19/02/2019 191277 4024257978
24/02/2019 241617 4024257978
16/02/2019 161996 4024257978
01/03/2019 011067 4024257978
14/03/2019 141250 4024257978
16/03/2019 161887 4024257978
25/03/2019 251565 4024257978
28/03/2019 281069 4024257978
16/01/2019 162098 4002086208
28/01/2019 281467 4002086208
15/02/2019 152312 4002086208
27/02/2019 272035 4002086208
13/02/2019 132036 4002086208
02/03/2019 020033 4002086208
03/03/2019 032104 4002086208
08/03/2019 082347 4002086208
14/03/2019 140032 4002086208
17/03/2019 171740 4002086208
21/03/2019 212400 4002086208
24/03/2019 241937 4002086208
24/03/2019 242063 4002086208
29/03/2019 292273 4002086208
26/03/2019 261603 4002086208
07/01/2019 071161 4062512262
13/01/2019 130742 4062512262
14/01/2019 140186 4062512262
15/01/2019 150229 4062512262
24/01/2019 240296 4062512262
24/01/2019 240403 4062512262
16/01/2019 160354 4062512262

Required output when i select March 2019
Reference Number Occurences 1 Month Occurences 3 Month
4024257978 5 17
4002086208 10 15

Please help me to get the dimension working as in required output

Re: Dynamic Dimension

anushree1 — Fri, 17 May 2019 08:05:18 GMT

I have loaded the table through inline load, and created Year and Month Columns as below:

Table:
Load *,
Month(Date) as Month,
Year(Date) as Year;
load * Inline
[
Date, AcNo, Reference Number
03/01/2019, 030245, 4024257978
06/01/2019, 060470, 4024257978
08/01/2019, 080510, 4024257978
09/01/2019, 090917, 4024257978
27/01/2019, 270107, 4024257978
05/02/2019, 051406, 4024257978
07/02/2019, 070309, 4024257978
09/02/2019, 091672, 4024257978
18/02/2019, 180504, 4024257978
19/02/2019, 191277, 4024257978
24/02/2019, 241617, 4024257978
16/02/2019, 161996, 4024257978
01/03/2019, 011067, 4024257978
14/03/2019, 141250, 4024257978
16/03/2019, 161887, 4024257978
25/03/2019, 251565, 4024257978
28/03/2019, 281069, 4024257978
16/01/2019, 162098, 4002086208
28/01/2019, 281467, 4002086208
15/02/2019, 152312, 4002086208
27/02/2019, 272035, 4002086208
13/02/2019, 132036, 4002086208
02/03/2019, 020033, 4002086208
03/03/2019, 032104, 4002086208
08/03/2019, 082347, 4002086208
14/03/2019, 140032, 4002086208
17/03/2019, 171740, 4002086208
21/03/2019, 212400, 4002086208
24/03/2019, 241937, 4002086208
24/03/2019, 242063, 4002086208
29/03/2019, 292273, 4002086208
26/03/2019, 261603, 4002086208
07/01/2019, 071161, 4062512262
13/01/2019, 130742, 4062512262
14/01/2019, 140186, 4062512262
15/01/2019, 150229, 4062512262
24/01/2019, 240296, 4062512262
24/01/2019, 240403, 4062512262
16/01/2019, 160354, 4062512262
];

Attached is an excel created a straight Table Dimensions and Expressions as per Excel Headers , and you will get the output as seen in the Excel, hope this helps

Re: Dynamic Dimension

prrajendran — Fri, 17 May 2019 09:17:19 GMT

I dont want to display reference number 4062512262 in the dimension because it has not occurred in my selected month.
i have my expressions working.
i want to do conditional dimension for the above case.

Re: Dynamic Dimension

anushree1 — Fri, 17 May 2019 09:55:32 GMT

Try using the below expressions and supress null values in presntation tab, this should take care of dimensions:

Exp 1: if(count([Reference Number])>0,count([Reference Number]))

Exp 2: if(count([Reference Number])>0,Count({<Month>}[Reference Number]))