Need help in writing formula for calculating sum of Invoices using set Analysis

[Akshaya]

Hi All,

I am new to Qlik sense, and need help in writing a formula using Set Analysis.

Scenario is as below: 
These are the main columns I am dealing with. So I have to calculate the sum(Invoice Actual(CC)) for any particular item for last one year. So for that I need to convert the date format of the 'SKU_RAT_DATE' from MON-YEAR to DD/MM/YYYY. E.g. for first item in the below image  'SKU_RAT_DATE'  is DEC 2021, so I need to convert it as 01/12/2021.

If 'SKU_RAT_DATE'  is JAN 2020 for any item, so I need to convert it as 01/01/2020.(P.S. I need to take 1st day of any given month).

Then once date conversion is done, for calculating the sum(Invoice Actual(CC)),  say if we take the first item in the below image, date is DEC 2021, so I need to go 1 year back i.e. DEC 2020 and calculate the sum from DEC 2020 to DEC 2021.

Considering one more example, suppose for any particular item 'SKU_RAT_DATE' has the value FEB 2020. So  I need to convert this date as  01/02/2020 and I need to go 1 year back(01/02/2019) and  calculate the sum(Invoice Actual(CC)) from 01/02/2019 to 01/02/2020.  (P.S. I need to take SKU_RAT_DATE into consideration for calculation for each item)

Please guide me here!

Thankyou,

Akshaya 

[GeorgePhilips23 · Accepted solution]

Date Conversion:

Makedate(Year(Date#(Right(SKU_Date,4),'YYYY')),month(Date#(left(SKU_Date,3),'MMM'))) as Modified_Date

12 months Set expression:

Sum({<Modified_Date={">=$(=ADDMONTHS(MAX(Modified_Date), -12))<=$(=MAX(Modified_Date))"}>}Invoice_Actual)

[Akshaya]

Hi George,

Thanks for the formulas!!

When I am using the Date Conversion formula, its working fine (and i am getting the conversion results too) till Makedate(Year(Date#(Right(SKU_Date,4),'YYYY')),month(Date#(left(SKU_Date,3),'MMM'))), but as soon as i am adding "as Modified_Date"  to it , it says error in expression.

PFA for your reference:

 

and since I am unable to give alias name for the date conversion formula, hence not sure how to use Set expression which included the Alias name(Modified_Date) from the Date Conversion.

Please Advise.

Thanks Much!

Akshaya

[MayilVahanan]

Hi Akshaya,

@GeorgePhilips23  advised you to create in the "scripting" part to bring new column and use that column in your set analysis.

Makedate(Year(Date#(Right(SKU_Date,4),'YYYY')),month(Date#(left(SKU_Date,3),'MMM'))) as Modified_Date

[Akshaya]

Hi Mayil,

Got it now!

Thankyou so much @GeorgePhilips23  and @MayilVahanan .

Regards,

Akshaya

[Akshaya]

Hi @GeorgePhilips23 / @MayilVahanan ,

One quick question here, I have used the same formulas/expressions as provided by George above as it is and it worked also.

Please see below :

In this excel, I selected a particular item, and got these many rows.  I am doing calculation for TTM Price(Trailing Twelve Month Price). Formula behind this TTM is as below:

sum(Invoice Actual(cc)) for last 12 months/sum(Invoice Primary UOM Qty) for last 12 months.

=Sum({< Modified_Date={">=$(=ADDMONTHS(MAX(Modified_Date), -12))<=$(=MAX(Modified_Date))"},
REC_TYPE={InvoiceDateRec}>} (RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE))/Sum({< Modified_Date={">=$(=ADDMONTHS(MAX(Modified_Date), -12))<=$(=MAX(Modified_Date))"},
REC_TYPE={InvoiceDateRec}>} (ARCOST_PRIMARY_QUANTITY))

This formula is working correctly as per its logic, Say for the selected particular item , the initial date was 1/12/21 and as of now the formula is going 12 months back i.e. 1/12/2020, and  adding up all the rows for sum of invoices  and Sum of Primary UOM qty by disregarding the 'Common_Date' as per below, and lastly dividing the to Get the TTM price., as highlighted below.

But i want the sums only for those rows for which the 'Common_Date' values lies in between the 1/12/2021 and 1/12/2020, or if i generalize my statement, i want the sums only for those rows for which 'Common_Date' values lies in the range of  

({<Modified_Date={">=$(=ADDMONTHS(MAX(Modified_Date), -12))<=$(=MAX(Modified_Date))"}>}

the logic that we wrote for date as in going 12 months back, so 'common_date' should come in between these 12 months , and i need sum of these rows only but not the all sum of all rows for any particular item.

Lastly, Please check the screenshots for excel and main app, i need the same values a per excel highlighted in grey and also i want that value only in the TTM column for all the rows of that particular item as highlighted in Grey.

Please help and suggest as its quite urgent.

Thankyou so much in advance!

Thanks,

Akshaya