Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

How to create new Variable based on another multiple Variable using if, else statement ??

 

Hi, I am looking for any advice on how to create variable and call in Qlik Sense report. 

Q. How to create a new variable using (filter, if, if else statement) on a different variable on the same calculated field? Please find the attached document of this power bi DAX Queries? Is the syntax similar in Qlik Sense or any option?

Power BI DAX Queries given below. I need syntax similar to these:-

Column1Column2Column3Column4Column5Permission.Check
1PermissionJohn AllowPermission
1PermissionJohn AllowPermission
1DeleteJohn AllowPermission
2PermissionJohn AllowDelete
2PermissionJohn AllowDelete
2DeleteJohn AllowDelete
2DeleteJohn AllowDelete
3PermissionJohn Allow 
3DeleteJohn Allow 
4PermissionJohnXYZAllow 
4DeleteJohn Deny 

 

Permissions.Check =

VAR Permissions = CALCULATE(COUNTROWS(TableName),ALLEXCEPT(TableName,TableName[column1],tableName[column2]))

VAR Delete =
    CALCULATE(
        COUNTROWS(TableName),
            FILTER(
                ALLEXCEPT(TableName,TableName[column1],TableName[column2]),
                    TableName[column3]="John"&&
                    ISBLANK(TableName[column4])
                )
            )

RETURN
SWITCH(TRUE(),
    TableName[column5]<>"Allow",BLANK(),

    Permissions=2&&Delete=1,"Permission",

    Permissions=2&&Delete=2,"Delete",

   BLANK()

)
Tags (3)
Labels (2)
8 Replies
Highlighted
Specialist II
Specialist II

Re: How to create Variable in Qlik Sense like PowerBI DAX queries?

You can find  a icon (x=) embed on the bottom left of the screen , click on that to create and define variables once a variable is defined you can call it in the expression window using $ Symbole like $(var_name)

Please refer:

https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/Variables/create-var...

https://www.youtube.com/watch?v=uFT-PNf_z9g 

Highlighted

Re: How to create Variable in Qlik Sense like PowerBI DAX queries?

I am not familiar with Power BI DAX Queries, so I don't understand what you are trying to do. Could you please describe the logic of what you are trying to achieve?

Further, I don't understand if you want to add a calculated column in the data model, or if you want to do it in a table in the UI. I assume that it is in the UI, but you don't mention anything about dimensions in your table, so I am not sure.

Anyway, if it is in the data model, you should just add a field in the Load statement in the script. But if it is in the UI, you need to create a measure in the object.

In both cases, you need to have an expression like If(<Condition>,'Permission','Delete') to define the content of the column, and here you can see where you need to define your logic.

Finally, in the UI the condition needs to use aggregation functions, otherwise it will not always be defined.

Highlighted
Contributor II
Contributor II

Re: How to create Variable in Qlik Sense like PowerBI DAX queries?

Hi Henric,

Can you provide the example of aggregation functions that you have written above?

Highlighted

Re: How to create Variable in Qlik Sense like PowerBI DAX queries?

Highlighted

Re: How to create new Variable based on another multiple Variable using if, else statement ??

I am trying to help your colleague may be with the same problem, but I don't really understand this well enough to answer

https://community.qlik.com/t5/New-to-Qlik-Sense/Create-variable-and-use-in-Calculated-field-or-varia... 

Highlighted
Contributor II
Contributor II

Re: How to create new Variable based on another multiple Variable using if, else statement ??

Hello Sunny

We have created Variable in Power Bi which is coming up in Columns, and here what we are looking for is that We need to create a Dimension which end up showing the result in the column.Also is it possible to create the Dimension through Variable ?
I believe you might have understood my concern now. Kindly help for the same.

 

Highlighted

Re: How to create new Variable based on another multiple Variable using if, else statement ??

You can do a lot with a Qlik variable, but don't let the name "variable" fool you. In the Qlik environment there is probably another, better way to do it: Perhaps you should define a master measure.

Do the following:

1) Figure out whether you want this new column in the data model (on the lowest, atomic level of data) or in the UI (aggregated numbers, recalculated based on the user selection)
2) Figure out what the expression should be. Test it to make sure that it does what you want.
3) Figure out how you can make this expression re-usable. Probably you should define a master measure. (But perhaps a master dimension is a better option? Or you can put it in a variable that is expanded to the wanted expression, whenever the variable is used.)

As you see, you should NOT start with the decision "I want a variable". You should start with bullet 1.

Good luck

HIC

Highlighted
Contributor II
Contributor II

Re: How to create new Variable based on another multiple Variable using if, else statement ??

Now it Solved!

Now I have a question regarding dimension.

Q. We have created dimensions, And we needed to create a count value in Dimension which ends up showing the result in the column (Table Chart).

Thank You!