topic Re: non changing dimension in QlikView

non changing dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

Is it possible to make a dimension not changing at all for any filters?

For example I have the following straight table

Month	Qnums	Sales
Jan	Q1234	100
Feb	Q2434	100
Mar	Q234	200
Apr	Q5456	500
May	Q565	300

For this table if for particular selection we have data for only Mar and Apr

So the table will reduce in size showing only Mar and Apr like this

Month	Qnums	Sales
Mar	Q234	200
Apr	Q5456	500

But i want in this manner

Month	Qnums	Sales
Jan	0	0
Feb	0	0
Mar	Q234	200
Apr	Q5456	500
May	0	0

Keeping the month dimension to be constant not changing.

Is it possible?

Any help would be appreciated

Thanks,

Anupama Jagan

Re: non changing dimension

MK_QSL — Mon, 15 May 2017 12:45:57 GMT

Create a Straight Table

Dimension

Month

Expression

1) IF(SubStringCount(Concat(DISTINCT Month, '|'),Month),Only({<Month>}Qnums),0)

2) IF(SubStringCount(Concat(DISTINCT Month, '|'),Month),SUM({<Month>}Sales),0)

Go to Presentation tab and untick Suppress Zero Values

Re: non changing dimension

Anonymous — Mon, 15 May 2017 12:50:15 GMT

Enable " Suppress When Value is Null " & " Show All Values " under Dimensions Tab.

Remove " Suppress Zero - Values " under Presentation Tab

Hope it Helps!!

Re: non changing dimension

asgardd2 — Mon, 15 May 2017 12:52:59 GMT

Hello!

1. Uncheck "Suppress Zero-Values" on Presentation tab:

2. Create expressions :

For sales:

Sum({<Month = >} Aggr(Sum(Sales),Month)

For Qnums:

IF(Count(Qnums)=0,0,Qnums)

Re: non changing dimension

Anonymous — Mon, 15 May 2017 13:14:47 GMT

Hi Anton ,

I have a calculated dimension

Month

if(Month <= Month(today()),Month)

Expressions are :

CYTD:

if(Month <= Month(today()), rangesum(above(total sum({Year = {$(=Max(Year))}, Met = {"Approved"}>}Revenue), 0, RowNo(TOTAL))))

PYTD

if(Month <= Month(today()), rangesum(above(total sum({Year = {$(=Max(Year)-1)}, Met = {"Approved"}>}Revenue), 0, RowNo(TOTAL))))

I'm not able to incorporate your change in this.

Thanks,

Anupama Jagan

Re: non changing dimension

Anonymous — Mon, 15 May 2017 13:15:36 GMT

Hi Anil,

Im not getting even after doing it

Thanks,

Anupama Jagan

Re: non changing dimension

Anonymous — Mon, 15 May 2017 13:17:57 GMT

Hi Manish

I'm not able to incorporate your logic in my expressions. Can you let me know how to go about it?

I have a calculated dimension

Month

if(Month <= Month(today()),Month)

Expressions are :

CYTD:

if(Month <= Month(today()), rangesum(above(total sum({Year = {$(=Max(Year))}, Met = {"Approved"}>}Revenue), 0, RowNo(TOTAL))))

PYTD

if(Month <= Month(today()), rangesum(above(total sum({Year = {$(=Max(Year)-1)}, Met = {"Approved"}>}Revenue), 0, RowNo(TOTAL))))

Thanks for your help

Regards,

Anupama Jagan

Re: non changing dimension

MK_QSL — Mon, 15 May 2017 13:19:37 GMT

Provide sample app here. We will work on it and try to give you solution.

Re: non changing dimension

Anonymous — Mon, 15 May 2017 13:25:38 GMT

Hi Manish,

I dont have any sample app with me as we work in remote desktop.

Maybe i will provide you sample data . will that be okay?

thanks,

Anupama Jagan

Re: non changing dimension

MK_QSL — Mon, 15 May 2017 13:27:44 GMT

Re: non changing dimension

Anonymous — Mon, 15 May 2017 13:31:41 GMT

I have now attached a sample data. It has year,month,revenue and client fields. so if we select any clicnet the month has to show always till current month.

the dimension and the expressions used in the application is given above.

Thanks,

Anupama Jagan

Re: non changing dimension

MK_QSL — Mon, 15 May 2017 13:52:37 GMT

Something like this..

Re: non changing dimension

vinieme12 — Mon, 15 May 2017 13:56:16 GMT

use the below

CYTD:

if(Month <= Month(today()), rangesum(above(total sum({1<Year = {$(=Max(Year))}, Met = {"Approved"}>}Revenue), 0, RowNo(TOTAL))))

PYTD

if(Month <= Month(today()), rangesum(above(total sum({1<Year = {$(=Max(Year)-1)}, Met = {"Approved"}>}Revenue), 0, RowNo(TOTAL))))

Re: non changing dimension

Anonymous — Tue, 16 May 2017 06:06:59 GMT

Hi Manish,

I find all the previous year revenue to be zero. May I know what have you done in this?

Thanks,

Anupama Jagan

Re: non changing dimension

Anonymous — Tue, 16 May 2017 06:09:02 GMT

Hi Vineeth

But the values will not change for any filters right?

Thanks,

Anupama Jagan

Re: non changing dimension

tyagishaila — Tue, 16 May 2017 06:34:07 GMT

For Month Dimension

Check Show All Values

Re: non changing dimension

Anonymous — Tue, 16 May 2017 06:36:25 GMT

Hi Shaila,

For me that option isn't enabled at all..is it becauseI have used a calculated dimension?

Re: non changing dimension

tyagishaila — Tue, 16 May 2017 07:10:17 GMT

what output you required?

Re: non changing dimension

Anonymous — Tue, 16 May 2017 07:20:40 GMT

Hi Shaila,

These are my Dimensions and Expressions:

Month

if(Month <= Month(today()),Month)

Expressions are :

CYTD:

if(Month <= Month(today()), rangesum(above(total sum({Year = {$(=Max(Year))}, Met = {"Approved"}>}Revenue), 0, RowNo(TOTAL))))

PYTD

if(Month <= Month(today()), rangesum(above(total sum({Year = {$(=Max(Year)-1)}, Met = {"Approved"}>}Revenue), 0, RowNo(TOTAL))))

So as per my calculated dimesion the straight table will display only till current month ..So Jan- May will be displayed.

But for few selections there might be entry for few months. For example for a particular selection feb month values might not be there. So in the table we will have Jan,Mar,Apr and May be displayed.

But I want Feb to be displayed as well. Kindly let me know if stil requirement is unclear

Any help would be appreciated

Re: non changing dimension

vinieme12 — Tue, 16 May 2017 07:21:04 GMT

have you tried yet? please try and let us know