topic Re: SetAnalysis in QlikView

SetAnalysis

dseelam — Fri, 01 Sep 2017 22:41:25 GMT

Hello All,

Some one explain me below logic in simple terms?

sum({$<CancelDateNum_OO={">=$(vCurrentMonthStartDateNum) <=$(vMaxShipDateNum)"}=>} Quantity_OO *UNIT_SELLING_PRICE_OO)

Thanks,

Dinesh

Re: SetAnalysis

rohitraut — Sat, 02 Sep 2017 03:36:22 GMT

You will get sum of (Quntity*unitsellingprice) cancel date those current month start date number is greater than is equal to and less than is equal to max ship date number

Re: SetAnalysis

prma7799 — Sat, 02 Sep 2017 04:09:51 GMT

It will give sum of value for " Quantity_OO *UNIT_SELLING_PRICE_OO" with range between

vCurrentMonthStartDateNum and vMaxShipDateNum

Re: SetAnalysis

Kushal_Chawda — Sat, 02 Sep 2017 06:00:18 GMT

This s giving you Sum of Sales for Date CancelDateNum_OO range between vCurrentMonthStartDateNum &vMaxShipDateNum

For eg.

Sum of Sales where CancelDateNum_OO between '01/08/2017' and '31/08/2017'

Here,vCurrentMonthStartDateNum='01/08/2017'

vMaxShipDateNum='31/08/2017'

Re: SetAnalysis

its_anandrjs — Sat, 02 Sep 2017 06:09:07 GMT

The above expression gives Sum and product of ( Quantity * Unit ) Quantity_OO *UNIT_SELLING_PRICE_OO

and after that give SUM or SUM( Quantity_OO *UNIT_SELLING_PRICE_OO ) BUt in the particular date whether it is selected by calendar object or dynamically.

Ex:-

vCurrentMonthStartDateNum = 01/09/2017

vMaxShipDateNum = 02/09/2017 or (depend on selection ex:- 30/09/2017)

Which is done by this SET expression CancelDateNum_OO={">=$(vCurrentMonthStartDateNum) <=$(vMaxShipDateNum)"}=>}

Re: SetAnalysis

Anonymous — Sat, 02 Sep 2017 13:49:02 GMT

It will give you the Sum of [Quantity_OO *UNIT_SELLING_PRICE_OO] on Current selection data set

only for the records having CancelDateNum_OO value in the range >=$(vCurrentMonthStartDateNum) and <=$(vMaxShipDateNum).

For Example.

vCurrentMonthStartDateNum='08/01/2017'

vMaxShipDateNum='08/15/2017'

It will give the sum of selling_price for records which are having CancelDateNum in between '08/01/2017' & '08/15/2017'.

Spliting the Set expression for easy understanding

Aggregation is sum(Quantity_OO *UNIT_SELLING_PRICE_OO)

Set identifier is $ (Current selection)

set Modifier Field is CancelDateNum_OO

Element list in Set Modifier Field is a Search String with a Range (It consider record having CancelDateNum_OO Value greater than eqaul to $(vCurrentMonthStartDateNum) but less than equal to $(vMaxShipDateNum))

Re: SetAnalysis

dseelam — Sat, 02 Sep 2017 22:55:26 GMT

Kushal,

Thanks for your reply

my issue hear is on starting of every month my numbers look bad, for example what if my

vCurrentMonthStartDateNum='01/09/2017'

vMaxShipDateNum='31/08/2017'

what result it give?

Thanks,

Re: SetAnalysis

dseelam — Sat, 02 Sep 2017 22:57:49 GMT

Thanks Karthik,

One question as I posted earlier

my issue hear is on starting of every month my numbers look bad, for example what if my

vCurrentMonthStartDateNum='01/09/2017'

vMaxShipDateNum='31/08/2017'

what result it give?

and how come I get around this issue ?

Thanks,

Re: SetAnalysis

Anonymous — Sun, 03 Sep 2017 14:36:06 GMT

Can you explain your exact requirement?

Try the below set expression using variables and let me know whether it fits to your requirement

sum({$<CancelDateNum_OO={" >=$(vDate1) <=$(vDate2) "}=>} Quantity_OO *UNIT_SELLING_PRICE_OO)

Variable declarations:

vDate1

if(num($(vCurrentMonthStartDateNum))<num(vMaxShipDateNum),$(vCurrentMonthStartDateNum),$(vMaxShipDateNum))

vDate2

if(num($(vCurrentMonthStartDateNum))<num($(vMaxShipDateNum)),$(vMaxShipDateNum),$(vCurrentMonthStartDateNum))

Re: SetAnalysis

Kushal_Chawda — Mon, 04 Sep 2017 07:18:17 GMT

what is expression in both variables?

Re: SetAnalysis

dseelam — Mon, 04 Sep 2017 22:37:40 GMT

Hey Karthik,

I will try provided expression!

my exact requirement

sum({$<CancelDateNum_OO={">=$(vCurrentMonthStartDateNum) <=$(vMaxShipDateNum)"}=>} Quantity_OO *UNIT_SELLING_PRICE_OO)

--this issue exists only on 1st of every month

Above expression should give me amount that got past cancelled from current month, September 1st it gave me eg:100000 $ but it is supposed to be 0$ since it is the first day of the month so there should be no orders with September cancel date that are past cancelled yet

CancelDateNum_OO= dates varies from 2014 to 2018

vCurrentMonthStartDateNum = num(MonthStart(vMaxShipDate))

vMaxShipDate=max(ship date) ship date is mostly yesterday in most cases except Sunday - what happens in if month end is Sunday

vMaxShipDateNum= num(vMaxShipDate)

Thanks,

Re: SetAnalysis

dseelam — Mon, 04 Sep 2017 22:38:39 GMT

Hello Kushal,

expression in both cases

vCurrentMonthStartDateNum = num(MonthStart(vMaxShipDate))

vMaxShipDate=max(ship date) ship date is mostly yesterday in most cases except Sunday - what happens in if month end is Sunday

vMaxShipDateNum= num(vMaxShipDate)

Re: SetAnalysis

qliksus — Tue, 05 Sep 2017 04:47:30 GMT

How does the Monthstart('31/08/2017') will give you '01/09/2017' ? are you using correct date format the issue in your case is your start date > end date hence the result is showing all the data less than the end date . Are you trying with proper date format