topic Re: Variables in Set Analysis in QlikView

Variables in Set Analysis

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

Dear All,

I want one help. I am attaching sample QVW file as well as sample excel file for this purpose.

In the qlikview document first text box is showing quantity for the month and FY in selection as correct.

Now I have defined three variables and created three input boxes for them and have selected the three desired values in those three input boxes. Now I am trying to use those variables in set analysis expression in second text box. I want the result to come to 61500 as can be seen from the sample excel file example, the row which I have marked as yellow Row No 34. I want to know what wrong I am doing in defining set analysis expression. The second text box is independent of any selection in the list boxes there.

Hope I have clarified in my qlikview document what I want.

Regards,

Manish Prasad

Re: Variables in Set Analysis

prma7799 — Wed, 28 Jun 2017 06:13:25 GMT

When I select 2017 then its come.

Re: Variables in Set Analysis

manpri7078 — Wed, 28 Jun 2017 06:23:49 GMT

I don't want to select 2017 in the list box. I want the second text box to show that figure and it should be independent of the selections in the two list boxes attached. For that purpose only I have created three input boxes with three different variables where user can select his desired values.

The selection is the list box is for the remaining objects in the whole document. I want only the second text box to calculate according to the three variables.

Hope I am clear in my query now.

Regards

Manish Prasad

Re: Variables in Set Analysis

sujeetsingh — Wed, 28 Jun 2017 06:29:14 GMT

First of all you can use this link to get the way of how to use variables in set nalysis.

Set Analysis with variables

Now to remove selection (or make independent of selection add Field= in set analysis expression.

Re: Variables in Set Analysis

manpri7078 — Wed, 28 Jun 2017 06:42:32 GMT

I am adding field= in my set analysis expression in my second text box. Just want to know what I am doing wrong.

My set analysis expression is Sum({<Period=,[Mon-Y]={'$(vInputMonth)'},Item_Grouping={'$(vInputExpense)'},Unit={'$(vInputUnit)'},Product={'Hydrate'}>}Actual_Qty)

What is wrong here ?

Re: Variables in Set Analysis

prma7799 — Wed, 28 Jun 2017 06:44:58 GMT

Please change your [Mon-Y] date format in MMM-YY and try it will work

Re: Variables in Set Analysis

prma7799 — Wed, 28 Jun 2017 06:49:49 GMT

PFA

Change input unit 1 to unit 2 in input box

Re: Variables in Set Analysis

manpri7078 — Wed, 28 Jun 2017 07:00:19 GMT

Your expression is calculating when any list box is not selected. But in my case these list boxes have selections which are being used in other objects. I want that keeping all the list boxes selected, the said text box to display 61400 which should be independent of any selections.

How to do it ?

Hope I am now more clear in my query.

Regards,

Manish Prasad

Re: Variables in Set Analysis

prma7799 — Wed, 28 Jun 2017 07:06:06 GMT

Try this

=Sum({<Period=,FY=,[Mon-Y]={'$(vInputMonth)'} ,Item_Grouping={'$(vInputExpense)'},Unit={'$(vInputUnit)'},Product={'Hydrate'}>}Actual_Qty)

Re: Variables in Set Analysis

manpri7078 — Wed, 28 Jun 2017 07:12:36 GMT

in this case also if list box of month is selected, then it is not calculating. There is something missing still.

Regards

Manish Prasad

Re: Variables in Set Analysis

prma7799 — Wed, 28 Jun 2017 07:14:53 GMT

Please try

=Sum({<Period=,FY=,Month=,Days=, [Mon-Y]={'$(vInputMonth)'} ,Item_Grouping={'$(vInputExpense)'},Unit={'$(vInputUnit)'},Product={'Hydrate'}>}Actual_Qty)

Re: Variables in Set Analysis

manpri7078 — Wed, 28 Jun 2017 07:18:33 GMT

No still not working. I have attached my final qlikview document after all these corrections.

Regards

Manish Prasad

Re: Variables in Set Analysis

prma7799 — Wed, 28 Jun 2017 07:20:54 GMT

You are missing this

LOAD

Particulars,

Item_Grouping,

Expense_Type,

Unit,

Product,

Month,

FY,

Period,

Days,

date([Mon-Y],'MMM-YY') as [Mon-Y] ,

Actual_Qty

FROM

[Sample Excel File (1).xlsx]

(ooxml, embedded labels, table is Data);

Re: Variables in Set Analysis

manpri7078 — Wed, 28 Jun 2017 07:24:35 GMT

Thanks. It is now working. I could not figure this problem that the load script has to be changed.

Regards,

Manish Prasad

Re: Variables in Set Analysis

manpri7078 — Wed, 28 Jun 2017 07:34:27 GMT

I am using following expression some where else :

Sum({<Period=,Item_Grouping={'Hydrate'},FY, Month,[Mon-Y]={">$(=Num(Addmonths(Max([Mon-Y]),-vInputRolling))) <=$(=Max([Mon-Y]))"}>}Actual_Qty)

In this expression, I am using another variable vInputRolling to make the bar chart for last n months if the current month is selected.

After making changes in the load script the above expression is not working now. Can you guide me what I have to do to this type of expressions which I am using in many objects in my working.

Regards

Manish Prasad

Re: Variables in Set Analysis

prma7799 — Wed, 28 Jun 2017 07:38:21 GMT

Please create new thread for this and close this thread

Re: Variables in Set Analysis

prma7799 — Wed, 28 Jun 2017 07:55:43 GMT

Please close this thread marking as correct or helpful answer.