topic Re: Question in QlikView

Question

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

Hi All:

I want to be able to do this statement:

1) if max week -1 is between weeks 40 to 44 then P10.

This is my formula for max(Week -1):

=Sum( {$<YEAR = {$(#=Max(YEAR))}, [Week No]= {$(#=Max([Week No])-1)}, CUSTOMER_TYPE -= {ITC}, CREDIT_CODE = {YES}>} AMOUNT )

This is my formula for max(period):

=Sum( {$<GLYEAR = {$(#=Max(GLYEAR))}, GLPERIOD= {$(#=Max(GLPERIOD))}, CUSTOMER_TYPE -= {ITC}, CREDIT_CODE = {YES}>} AMOUNT )

Thanks,

Re: Question

jadams123 — Sat, 03 Jun 2017 19:15:22 GMT

Basically what I want to do...if some one has an easier method is:

what ever (week-1) is grab that period and sum sales for the period.

Re: Question

jadams123 — Sat, 03 Jun 2017 19:18:12 GMT

oh by the way I want to keep in the formula somehow...

GLYEAR = {$(#=Max(GLYEAR))}

Re: Question

vinieme12 — Sat, 03 Jun 2017 19:51:05 GMT

Try below

=Sum( {$<YEAR = {$(#=Max(YEAR))}, GLPERIOD = P({<[Week No]= {$(#=Max([Week No])-1)}>}GLPERIOD), CUSTOMER_TYPE -= {ITC}, CREDIT_CODE = {YES}>} AMOUNT )

Re: Question

jadams123 — Sat, 03 Jun 2017 21:05:41 GMT

This formula worked perfectly.

Now I have a problem with YTD

I want to do...

Sum YTD till week-1

Re: Question

vinieme12 — Sat, 03 Jun 2017 21:17:00 GMT

Try below

=Sum( {$<YEAR = {$(#=Max(YEAR))}, [Week No]= {"<=$(#=Max([Week No])-1)"}, CUSTOMER_TYPE -= {ITC}, CREDIT_CODE = {YES}>} AMOUNT )

Re: Question

jadams123 — Sat, 03 Jun 2017 21:31:05 GMT

=Sum( {$<[Week No] = {"<$(#=Max([Week No]))"}, DateType = {'GL Date'}, CUSTOMER_TYPE = {*} - {ITC}, CREDIT_CODE = {YES}>} AMOUNT )

I am trying this formula...its giving me the correct answer.... but not sure if this is the correct way of doing it.

I thought I would be using week -1 some where.

Re: Question

vinieme12 — Sun, 04 Jun 2017 06:15:23 GMT

Are you sure this is giving you correct YTD?

You haven't specified Year anywhere so it would give you sum of week number less than example. week 1-35 for all possible years

Re: Question

jadams123 — Sun, 04 Jun 2017 16:26:36 GMT

ya you are right it was giving me all possible years...I have 2017 selected (even though I know the reason for an object is not to have any/limited selections)

If I clear all my selections this formulas doesn't give me correct answer

if today is week 2017 week 44 I wanted sales for 2017 week 43 -->[Week No]= {$(#=Max([Week No])-1)},

=Sum( {$<YEAR = {$(#=Max(YEAR))}, [Week No]= {"<=$(#=Max([Week No])-1)"}, CUSTOMER_TYPE -= {ITC}, CREDIT_CODE = {YES}>} AMOUNT ).

I know max is supposed to do this. but for some reason its not.

Re: Question

vinieme12 — Sun, 04 Jun 2017 18:27:06 GMT

[Week No]= {$(#=Max([Week No])-1)}, << I think it's because the "#' before the equal sign.....that # is not needed anywhere

Re: Question

jadams123 — Thu, 22 Jun 2017 12:02:06 GMT

My IT dept told me that in order for this code to work I have to select 2017 and weeks 1-XX. And its working.

Its not the perfect code...but it is working.

Re: Question

vinieme12 — Tue, 27 Jun 2017 07:24:17 GMT

Then try MAx with the TOTAL keyword

Sum( {$<YEAR = {$(=Max(TOTAL YEAR))}, [Week No]= {"<=$(=Max(TOTAL [Week No])-1)"}, CUSTOMER_TYPE -= {ITC}, CREDIT_CODE = {YES}>} AMOUNT ).