topic Re: Ignore selections on dimension in QlikView

Ignore selections on dimension

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have data from January 2015 to April 2016

I have created a line chart with different months and want to show all the months available irrespective of any selection except Year.

Dimesnsion : Month

Expression : If(Isnull(1-(Column1/Column2)),1,(1-(Column1/Column2)))

Case 1: If I select Year as 2015, I want to show all months(Jan - Dec) irrespective of any selection

Case 2 : If I select Year as 2016, I want to show only 4 Months (Jan - April) irrespective of any selection

on the dimension.

PS : Show all values will fail for Case 2 as it will show all the months and not only 4.

Also, there are no values for some Month - Year. Where it should display 1 as per the expression.

Regards,

Anjali Gupta

Re: Ignore selections on dimension

sunny_talwar — Wed, 15 Jun 2016 18:42:53 GMT

What are your expressions for Column2 and Column2? Would you be able to share a sample?

Re: Ignore selections on dimension

vikramv — Wed, 15 Jun 2016 18:45:07 GMT

Try this option on Month

Re: Ignore selections on dimension

Wed, 15 Jun 2016 18:48:49 GMT

No Sunny, I can't share the sample because of security purpose and also its connected to different mapping sheets. So, might be confusing.

Regards,

Anjali Gupta

Re: Ignore selections on dimension

Wed, 15 Jun 2016 18:51:01 GMT

Hi Vikram,

As mentioned earlier, Show All values is showing all the months for Case 2 i.e. on selection of year 2016.

Regards,

Anjali Gupta

Re: Ignore selections on dimension

sunny_talwar — Wed, 15 Jun 2016 18:54:37 GMT

I guess I am unable to completely understand what you are wanting to do. That is why I was hoping for few rows of sample dummy data with the expected output on the chart.

Re: Ignore selections on dimension

vikramv — Wed, 15 Jun 2016 19:12:05 GMT

Are you looking for something like this :

Sample Expression :

if( GetFieldSelections(Year) = '2016',sum({$<Month ={1,2,3,4}>} Sales),Sum(Sales))

if( GetFieldSelections(Year) = '2016',Sum({$<Month ={'January','February','March','April'}>} Sales),Sum(Sales))

Re: Ignore selections on dimension

Wed, 15 Jun 2016 19:13:34 GMT

Hi Sunny,

Say i have 2 fields in a mapping table

Field1	Field2
A	AA
B	BB
C	CC
D	DD
E	EE
F	FF
G	GG
H	HH
I	II
J	JJ
K	KK
L	LL

Then I have a data table

with few Columns and Field2

Say

UniqueID	Date	Column1	Column2	Field2
1	10-01-2015	45	1400	AA
2	23-06-2015	34	6400	DD
3	06-03-2016	24	3400	HH
4	17-08-2015	25	3540	DD
5	18-11-2015	45	4534	CC
6	27-01-2016	23	5675	HH
7	28-04-2016	45	4500	BB

With the Dates, I can say I have data from Jan-2015 to April 2016.

For all selection of Field1 [this is from mapping table] (Always One Selected) from a listbox, I want to show a trend (Line chart) for all the months.

If no data then 1 else 1-(Coulmn1/Column2) .

When i have checked Show All Values , the case 2 failed as mentioned above and shown data for May-2016 to December-2016. Which are future dates or data is yet to be received by me.

Hope this can make you help me..!!

Regards,

Anjali Gupta

Re: Ignore selections on dimension

Wed, 15 Jun 2016 19:15:01 GMT

Hi Vikram, Thanks for the quick response. But Is there any way to restrict it in the dimension itself ?

Regards,

Anjali Gupta

Re: Ignore selections on dimension

vikramv — Wed, 15 Jun 2016 19:18:45 GMT

Creat a dummy dimension for month and use

if( GetFieldSelections(Year) = '2016' and Match(Month,1,2,3,4), Dummy Dimension,Original Dimension)

Re: Ignore selections on dimension

Wed, 15 Jun 2016 19:21:12 GMT

Can you please help me on creating the dummy dimension?

If you can tell me step by step, it would be great.

Regards,

Anjali gupta

Re: Ignore selections on dimension

vikramv — Wed, 15 Jun 2016 19:25:38 GMT

I guess you can use this directly in for Month dimension without any dummy field

if( GetFieldSelections(Year) = '2016' and Match(Month,1,2,3,4), Month,Month)

Re: Ignore selections on dimension

sunny_talwar — Wed, 15 Jun 2016 19:34:00 GMT

So regardless of selection in Field1, you want to see all these 7 UniqueID on your chart?

Re: Ignore selections on dimension

Wed, 15 Jun 2016 19:40:03 GMT

No Sunny, I want regardless of any selection the dimension should change.

Apologies on missing sum() in expression

The correct expression is if(Isnull(1-(sum(Column1)/Sum(Column2))),1,(1-(sum(Column1)/Sum(Column2)))

I want to calculate this for each Field1

for E,F,G,I,J and K of Field1 it should be 1 for all the months.

Regards,

Anjali Gupta

Re: Ignore selections on dimension

Wed, 15 Jun 2016 19:40:40 GMT

Hi Vikram,

This is also not working for me.

Regards,

Anjali Gupta

Re: Ignore selections on dimension

sunny_talwar — Wed, 15 Jun 2016 19:42:40 GMT

No Sunny, I want regardless of any selection the dimension should change.

Not sure I understand this statement? Did you miss a 'NOT' here?

Re: Ignore selections on dimension

ziadm — Wed, 15 Jun 2016 20:06:10 GMT

if you have made a master calendar having the max date as of last Date in your fact Table and Min Date is the starting Date of you data, then selecting Year 2016 will give you only up to current months as your dimension.

I am not sure why you have make changes to the chart set analysis

Re: Ignore selections on dimension

maksim_senin — Thu, 16 Jun 2016 06:31:23 GMT

Hi,

Why not to use set analisys? E.g. Sum({<Month=,Week=,Date=>} Amount) will ignore selection in Month, Week and Date fields.

Best regards,

Maxim

Re: Ignore selections on dimension

Fri, 17 Jun 2016 04:08:24 GMT

Yes I missed it.

I want regardless of any selection, the dimension should not change.

Regards,

Anjali Gupta

Re: Ignore selections on dimension

Fri, 17 Jun 2016 04:13:20 GMT

Yes, you are correct that on selection it gives me a particular month. But it misses the month where there is No Data.

Example:

If I have data for May, Aug and Nov

It will show only these 3 months in the chart and not all the 12 months for 2015.