topic Re: Set Analysis with variables in QlikView

Set Analysis with variables

Thu, 03 Aug 2017 07:50:15 GMT

Hello All,

We are trying to improvise a dashboard

Set Analysis expression looks like :

=(if(Match(

[Task Name],

'ABC',

'FMG',

'ELT',

),1,(if(Match([Task Name],'PF'),Count({<Distribution={1},[Task Category]={"main","sub"},[Task Name]-={'*Distribution*','*Tiina*','*new Report*', '*housekeeping*'},Node_TS_Date={">=$(=Date(Today()-30))<$(=Date(Today()))"}, [Task Status]={'Completed', 'Warning','Failed'}>}Node_TS_Hour),2)))

I want to use variables in this for reuse purpose.

vCategory = [Task Category]={"main","sub"}

vTaskName = [Task Name]-={'*Distribution*','*Tiina*','*new Report*', '*housekeeping*'}

vTaskStatus = [Task Status]={'Completed', 'Warning','Failed'}

vTaskOneDay = Match([Task Name],'ABC','FMG','ELT',)

I am unable to create a set analysis expressions with these variables.

I have put this in one variable

vExpectedPCG = if(Match([Task Name],'PF'),Count({<Distribution={1},[Task Category]={"main","sub"},[Task Name]-={'*Distribution*','*Tiina*','*new Report*', '*housekeeping*'},Node_TS_Date={">=$(=Date(Today()-30))<$(=Date(Today()))"}, [Task Status]={'Completed', 'Warning','Failed'}>}Node_TS_Hour),2)

=if((=$('vTaskOneDay')),1, $(=vExpectedPCG))

It is giving result for all except vTaskOneDay part.

Can someone please help on this.

Re: Set Analysis with variables

zhadrakas — Thu, 03 Aug 2017 07:55:05 GMT

try to write it like this

'$(=vTaskOneDay)'

Re: Set Analysis with variables

Thu, 03 Aug 2017 08:03:28 GMT

Hello Tim,

Tried with this expression : =if('$(=vTaskOneDay)',1, $(=vExpectedPCG)) No luck

VariableExpectedReloadCount is with above expression giving correct for all except ABC . count should be 1

Task Name	Reload date	variableExpected reloadCounts	Expected reloadCounts
ABC	2017-07-18	2	1
XYZ	2017-07-18	2	2
PF	2017-07-18	3	3

Re: Set Analysis with variables

Thu, 03 Aug 2017 09:02:39 GMT

Hello All,

Variable :

vTaskOneDay = 'ABC','FMG','ELT'

Partially it worked with

=(if(Match([Task Name],$(vTaskOneDay )),1, $(=vExpectedPCG)))

Still working on the other part.

Re: Set Analysis with variables

Thu, 03 Aug 2017 09:27:05 GMT

Hello All,

vEDXtaskname = 'PF','HG',SK'

trying to do but still getting error.

vExpectedPCG = if(Match([Task Name],$(=vEDXtaskname)),Count({<Distribution={1},[Task Category]={"main","sub"},[Task Name]-={'*Distribution*','*Tiina*','*new Report*', '*housekeeping*'},Node_TS_Date={">=$(=Date(Today()-30))<$(=Date(Today()))"}, [Task Status]={'Completed', 'Warning','Failed'}>}Node_TS_Hour),2)

Regards,

Nisha

Re: Set Analysis with variables

Kushal_Chawda — Thu, 03 Aug 2017 09:37:40 GMT

How many task names you have?

Re: Set Analysis with variables

Thu, 03 Aug 2017 10:23:03 GMT

Hello Kushal,

As of now we have 3 in task names but can change in future.

Re: Set Analysis with variables

Kushal_Chawda — Thu, 03 Aug 2017 10:33:19 GMT

But in your expression looks like more,

'ABC',

'FMG',

'ELT',

'PF'

Re: Set Analysis with variables

Thu, 03 Aug 2017 11:15:49 GMT

Hello Kushal,

That way we have 100+ task, some run once a day, Some on EDX and some run twice.

We need to see the accuracy on what we are reloading based on the numbers it should be .

Hence task list may vary.

Regards,

Nisha

Re: Set Analysis with variables

mjtaft2017 — Fri, 04 Aug 2017 14:42:10 GMT

Hello Nisha

Maybe try what worked for your other variable when you put it in the expression and got desired result --

"Partially it worked with =(if(Match([Task Name],$(vTaskOneDay )) "

so this --

=if(Match([Task Name],$(vEDXtaskname)) ....