topic Re: Use of Variable in Set in QlikView

Use of Variable in Set

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

Hi I am trying to showcase numbers in a text object using Set.

Here is my expression

=({$<P_Month={"$(=Date(Addmonths(Month_Name,-1),'MMMM'))"}>}$(TPH))

now TPH is a variable. Its definition : num((($(Pro_T)+$(Qct))/$(Avt))*100,'##.00')

Again explanation of each variable is :

Pro_t: num(sum(Processing_Time)/60,'##.00')

Qct: num(sum(QC_Time)/60,'##.00')

Avt: num((sum(Attendance))*7.5*.89,'##.00')

I need to calculate the result for last month for which set is there.

I cant make it work using different logics. please assist me here to get a solution.

Regards

Sagar

Re: Use of Variable in Set

avinashelite — Mon, 04 Sep 2017 07:35:16 GMT

What's the issue your facing ???

Re: Use of Variable in Set

Anil_Babu_Samineni — Mon, 04 Sep 2017 07:35:18 GMT

What are you getting when you use this alone in text box? =$(TPH)

Re: Use of Variable in Set

sagar_vij99 — Mon, 04 Sep 2017 07:36:53 GMT

it is showing error ')' missing

Re: Use of Variable in Set

sagar_vij99 — Mon, 04 Sep 2017 07:37:31 GMT

it will give result but i want to calculate for previous month also. that is why i am using set.

Re: Use of Variable in Set

Anil_Babu_Samineni — Mon, 04 Sep 2017 07:39:42 GMT

That is what i was over thinking the issue. Here, You need to set some thing like this

=Sum ({$<P_Month={"$(=Date(Addmonths(Month_Name,-1),'MMMM'))"}>}$(TPH))

So, Here i assume you have full month names

Re: Use of Variable in Set

sagar_vij99 — Mon, 04 Sep 2017 07:59:38 GMT

Hi Anil,

I tried the solution but i am getting error nested aggregation not allowed.

Re: Use of Variable in Set

Anil_Babu_Samineni — Mon, 04 Sep 2017 08:03:36 GMT

True, Because Sum(...Sum(...Sum())) won't allow.

Can you attach application to check

Re: Use of Variable in Set

sagar_vij99 — Mon, 04 Sep 2017 10:04:11 GMT

please see the attached file. there is a text object on right with error

Re: Use of Variable in Set

Anil_Babu_Samineni — Mon, 04 Sep 2017 10:50:32 GMT

I am thinking some thing like below Aggregations and Function Classes

Variable is

((num(Sum(Aggr(sum(Processing_Time),Month_Name))/60,'##.00') + num(Sum(Aggr(sum(QC_Time), Month_Name))/60,'##.00'))

/ num((Sum(Aggr(sum(Attendance), Month_Name)))*7.5*.89,'##.00'))*100

Expression

=Count({$<P_Month={"$(=Date(Addmonths(Month_Name,-1),'MMMM'))"}>} $(Variable))

POS - Change Month_Name to P_Month as needed

HTH

Anil

Re: Use of Variable in Set

Anil_Babu_Samineni — Mon, 04 Sep 2017 10:56:50 GMT

Try this as full expression

=Count({$<P_Month={"$(=Date(Addmonths(Month_Name,-1),'MMMM'))"}>}

((num(Sum(Aggr(sum(Processing_Time),Month_Name))/60,'##.00') + num(Sum(Aggr(sum(QC_Time), Month_Name))/60,'##.00'))

/ num((Sum(Aggr(sum(Attendance), Month_Name)))*7.5*.89,'##.00'))*100)

Re: Use of Variable in Set

Anil_Babu_Samineni — Mon, 04 Sep 2017 10:58:16 GMT

Or May be stalwar1‌ hic‌ helps you

Re: Use of Variable in Set

sagar_vij99 — Tue, 05 Sep 2017 05:24:07 GMT

Hi Anil, I found a solution and thought of sharing. I used three different variables and used them to calculate numbers using set and then did different aggregations to get the output. I hope thats correct and if you think there is any issue with it, please let me know.

Re: Use of Variable in Set

Anil_Babu_Samineni — Tue, 05 Sep 2017 06:08:57 GMT

Glad you found the answer only thing you should share behind the correct answer so then next land up can tackle easily...